当前位置:首页 » 《随便一记》 » 正文

「MySQL」- 约束详解_「zero」的博客

24 人参与  2022年04月24日 16:15  分类 : 《随便一记》  评论

点击全文阅读


目录

约束的认识

非空约束 ( not null )

1.创建数据表添加非空约束

2.现有数据表添加非空约束

3.删除非空约束

唯一约束 ( unique )

1.创建表添加唯一约束

2.现有数据表添加非空约束

3.删除唯一约束

默认约束 ( default )

1.创建表添加默认约束

2.现有表中添加默认约束

3.删除默认约束

主键约束 ( primary key )

1.创建表添加主键约束

2.自增主键

3.现有表中添加默认约束

4.删除主键约束

外键约束 ( foreign key )

1.创建表添加外键约束

2.在现有表中添加外键约束

3.删除外键约束

约束的认识

约束是一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性 , MySQL数据库通过约束防止无效的数据进入到表中,以保护数据的实体完整性 , 在 MySQL 中一共分为以下几种约束类型

约束类型说明
not null指示某列不能存储 NULL 值。
unique保证某列的每行必须有唯一的值。
default规定没有给列赋值时的默认值
primary keynot null 和 unique 的结合。 确保某列或多个列的结合有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
foreign key保证一个表中的数据匹配另一个表中的值的参照完整性

约束的应用场景一般分为以下两种

  1. 创建数据表的时候添加约束 

    create table <数据表名>(<列名1> <约束类型> , <列名2> <约束类型>,.......)
  2. 修改现有表的约束类型

    -- 给现有数据表添加字段并设置约束条件
    alter table <数据表名> add <字段名> <类型> <约束条件>;
    -- 修改现有数据表的约束条件
    alter table <数据表名> modify <字段名> <类型> <约束条件>;
    -- 
    alter table <数据表名> drop <约束名> <约束键名>;

非空约束 ( not null )

创建表时,可以指定某列添加非空约束 , 使其在添加数据时该列必须要添加有效的数据.

1.创建数据表添加非空约束

命令

create table <数据表名>(<列名> not null);

示例

在数据库中创建一张 student 数据表 , 并把 id 字段设置为非空约束

-- 重新设置学生表结构
drop table if exists student;
 -- 创建 student 数据表
create table student (
    id int not null,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

查看 student 表结构观察变化 , 可以看到 , 在 id 字段中的 Null 此时的状态是 NO , 意味着 , 在添加数据的时候 , id 字段不接受 null

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

如果在添加数据时 id 列为 null 或者不添加 id 列, 此时就会出现报错

-- 全列添加
mysql> insert into student values(null,2001,'张三','123@qq.com');
ERROR 1048 (23000): Column 'id' cannot be null
-- error : 列'id'不能为空

-- 指定列添加
mysql> insert into student(sn,name,qq_mail) values(2001,'张三','123@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- error : 字段id没有默认值

2.现有数据表添加非空约束

命令

alter table <数据表名> modify <字段名> <类型> not null;

示例

在现有 student 表中的 sn 字段添加非空约束

alter table student modify sn int not null;

查看表结构 , 可以看到 sn 字段中的 Null 选项的状态也变成了 NO

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sn      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.删除非空约束

命令

alter table <数据表名> modify <字段名> <类型>;

示例

在现有 student 表中 , 删除 sn 字段的非空约束

alter table student modify sn int;

查看表结构 , 可以看到 sn 字段中的 Null 选项状态变回了 YES

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

唯一约束 ( unique )

在创建数据表时 , 可以指定某列添加唯一约束 ,  使该列在添加数据时 , 不能添加重复的数据 

1.创建表添加唯一约束

命令

create table <数据表名>(<列名> unique);

示例

在数据库中创建一张 student 数据表 , 并把 id 字段设置为唯一约束

-- 重新设置学生表结构
drop table if exists student;
-- 创建 student 数据表
create table student (
    id int unique,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

查看 student 表结构观察变化 , 可以看到 , 在 id 字段中的 Key 此时的状态是 UNI ,代表当前 id 字段的值是唯一的 , 不接受重复值

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | UNI | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

如果在添加数据时 , 添加 id 列的数据是重复的 , 那么就会报错

-- 第一次添加数据
insert into student values(1,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

-- 第二次添加数据
insert into student values(1,2001,'张三','123@qq.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
-- error : 为键“id”复制项“1”

2.现有数据表添加非空约束

命令

-- 第一种方式
alter table <数据表名> add unique(<字段名>);
-- 第二种方式
alter table <数据表名> modify <字段名> <类型> unique;

示例

在现有 student 表中给 sn 字段添加唯一约束

alter table student add unique(sn);

上面这种添加方式也可以写成下面这种 , 效果一样

alter table student modify sn int unique;

查看 student 表结构 , 可以看到 sn 字段中的 Key 此时的状态变成了 UNI

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | UNI | NULL    |       |
| sn      | int(11)     | YES  | UNI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.删除唯一约束

删除唯一约束的方法不一样 , 需要先在表中先找到该唯一约束的约束名称

命令

alter table <数据表名> drop index <约束名>;

查看约束对应的约束名称

show create table <数据表名>;

示例

删除 student 表中 sn 字段的唯一约束

  1. 先查看 student 表中所有约束信息 , 找到 sn 对应的约束名

    show create table student;

    student 表对应的以下两个约束 , `` 引起的就是约束名
    UNIQUE KEY `id` (`id`),
    UNIQUE KEY `sn` (`sn`)
  2. 使用对应的键名删除 student 表中唯一约束

    alter table student drop index sn;
  3. 查看表结构 , 可以看到 sn 字段中的 Key 选项状态为空了

    mysql> desc student;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(11)     | YES  | UNI | NULL    |       |
    | sn      | int(11)     | YES  |     | NULL    |       |
    | name    | varchar(20) | YES  |     | NULL    |       |
    | qq_mail | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

默认约束 ( default )

在创建数据表时 , 给指定字段添加默认约束并设置默认值 , 在添加记录时如果该列未指定值 , 就会按照默认值填充

1.创建表添加默认约束

命令

create table <数据表名>(<列名> <类型> default <默认值>);

示例

在数据库中创建一张 student 数据表 , 并把 name 字段设置默认值约束 , 默认值为 "未命名"

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int,
    sn int,
    name varchar(20) default '未命名',
    qq_mail varchar(20)
);

查看 student 表结构 , 可以看到 , 在 name 字段中的 Default 选项的值已经被更改成 未命名

mysql> desc student;
+---------+-------------+------+-----+-----------+-------+
| Field   | Type        | Null | Key | Default   | Extra |
+---------+-------------+------+-----+-----------+-------+
| id      | int(11)     | YES  |     | NULL      |       |
| sn      | int(11)     | YES  |     | NULL      |       |
| name    | varchar(20) | YES  |     | 未命名    |       |
| qq_mail | varchar(20) | YES  |     | NULL      |       |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)

添加数据不指定 name 列 , 则会填充默认值

-- 不指定 name 字段添加数据
insert into student(id,sn,qq_mail) values(1,2001,'123@qq.com');

-- 全列查询
mysql> select * from student;
+------+------+-----------+------------+
| id   | sn   | name      | qq_mail    |
+------+------+-----------+------------+
|    1 | 2001 | 未命名    | 123@qq.com |
+------+------+-----------+------------+
1 row in set (0.00 sec)

2.现有表中添加默认约束

命令

alter table <数据表名> alter <字段名> set default <默认值>; 

示例

在现有 student 表中 , 给 qq_mail 字段添加默认值约束 , 默认值为 "未指定"

alter table student alter qq_mail set default '未指定';

查看 student 表结构观察 , qq_mail 字段中的 Default 选项的值已经被更改成 未指定

mysql> desc student;
+---------+-------------+------+-----+-----------+-------+
| Field   | Type        | Null | Key | Default   | Extra |
+---------+-------------+------+-----+-----------+-------+
| id      | int(11)     | YES  |     | NULL      |       |
| sn      | int(11)     | YES  |     | NULL      |       |
| name    | varchar(20) | YES  |     | 未命名    |       |
| qq_mail | varchar(20) | YES  |     | 未指定    |       |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)

3.删除默认约束

命令

alter table <数据表名> alter <字段名> drop default;

示例

删除 student 表中 name 字段的默认约束

alter table student alter name drop default;

查看 student 表结构 , 可以看到 name 字段中的 Default 选项的值已经返回为 null

mysql> desc student;
+---------+-------------+------+-----+-----------+-------+
| Field   | Type        | Null | Key | Default   | Extra |
+---------+-------------+------+-----+-----------+-------+
| id      | int(11)     | YES  |     | NULL      |       |
| sn      | int(11)     | YES  |     | NULL      |       |
| name    | varchar(20) | YES  |     | NULL      |       |
| qq_mail | varchar(20) | YES  |     | 未指定    |       |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)

主键约束 ( primary key )

主键约束其实就是 非空约束 和 唯一约束的组合 , 给指定字段添加主键约束 , 在添加数据时 , 该列不能为空且不能重复 ,一张数据表中只能有一个主键 ! ! !

1.创建表添加主键约束

命令

create table <数据表名>(<列名> <类型> primary key); 

示例

在数据库中创建一张 student 数据表 , 并把 id 字段设置主键约束

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int primary key,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

查看 student 表结构 , 可以看到在 id 字段中的 key 选项的状态变成了 PRINull 选项状态为 NO , 这就是主键约束的效果

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

student 表添加数据不指定 id 列或者添加的 id 列的值重复或为 null 时将会报错

-- 第一次添加
insert into student values(1,2001,'张三','123@qq.com');
-- 添加成功

-- 第二次添加
insert into student values(1,2001,'张三','123@qq.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- error : 键“PRIMARY”的重复项“1”

-- 添加记录 , id 列为 null
insert into student values(null,2001,'张三','123@qq.com');
ERROR 1048 (23000): Column 'id' cannot be null
-- error : 列'id'不能为空

-- 不指定 id 列添加记录
insert into student(sn,name,qq_mail) values(2001,'张三','123@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- error : 字段id没有默认值

2.自增主键

实际开发中,往往有一系列的策略来保证 主键 不重复,最简单粗暴的做法,就是"自增主键",MySQL 内置了自增主键的功能 (在 primary key 后面加上 auto_increment ==> primary key auto_increment),此时在一列中就成了自增主键,用户插入数据的时候可以不必手动干预了,如果当前 id 列为自增主键,这个时候 id 列是可以为null的,为 null 表示由MySql的自增主键自动进行自增

命令

create table <数据表名>(<列名> <类型> primary key auto_increment);

示例

在数据库中创建一张 student 数据表 , 并把 id 字段设置主键约束 , 并添加自增主键

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int primary key auto_increment,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

查看 student 表结构 , 可以看到 , id 字段中除了 Key 选项中状态为 PRINull 选项状态为 NO 之外 , 还有一个 Extra 选项的值也是 auto_increment

mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn      | int(11)     | YES  |     | NULL    |                |
| name    | varchar(20) | YES  |     | NULL    |                |
| qq_mail | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

再添加记录时 , 如果 id 列为 null , 此时自增主键会自动给 id 列添加一个值 , 能保证不重复

-- 添加记录 , id 列由自增主键默认给值
mysql> insert into student values(null,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

-- 添加记录 , id 列由自增主键默认给值
mysql> insert into student values(null,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

-- 添加记录 , id 列由自增主键默认给值
mysql> insert into student values(null,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)


-- 手动干预 id 列添加数据
mysql> insert into student values(10,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

-- 添加记录 , id 列由自增主键默认给值
mysql> insert into student values(null,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

查看表中所有列 , 添加语句 idnull 的命令自增主键都会默认添加一个值且每个值都不重复 , 如果手动干预超过了当前自增主键的最大值 , 自增主键就会从当前手动干预的值开始继续往下自增 , 这是因为自增主键在MySQL内部维护了一个"全局变量"的东西 , 每次添加的记录如果指定的是 null , 全局变量 ++ , 如果手动添加的值 , 超过当前的全局变量 , 全局变量则会直接更新成当前最大的值.

mysql> select * from student;
+----+------+--------+------------+
| id | sn   | name   | qq_mail    |
+----+------+--------+------------+
|  1 | 2001 | 张三   | 123@qq.com |
|  2 | 2001 | 张三   | 123@qq.com |
|  3 | 2001 | 张三   | 123@qq.com |
| 10 | 2001 | 张三   | 123@qq.com |
| 11 | 2001 | 张三   | 123@qq.com |
+----+------+--------+------------+
5 rows in set (0.00 sec)

3.现有表中添加默认约束

由于自增主键在一张表中只能有一个 , 所以这里我们需要重新设置以下 student 表结构 , 也就是先把 student 表先删除 , 然后创建一张没有任何约束的 student

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

-- 查看表结构
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

命令

alter table <数据表名> add primary key(<字段名>);

示例

student 表中的 id 字段设置为主键

alter table student add primary key(id);

查看 student 表结构 , 可以看到 id 列中的 Key 选项的状态已经改成 PRI 了 且 Null 选项状态为 NO

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.删除主键约束

命令

alter table <数据表名> drop primary key;

示例

删除 student 表中的主键约束

alter table student drop primary key;

查看表结构 , 发现虽然 id列中的 Key 选项的状态已经变成空白 , 但是 Null 选项中的状态还是 NO , 表示不允许填空值 , 所以要将效果移除干净还要执行一次删除非空约束的命令

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 删除非空约束
alter table student modify id int;

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

外键约束 ( foreign key )

外键用于关联其它表中的主键或唯一键 , 在插入数据时 , 必须插入另一张表中的主键的有效数据

先准备另一张带有主键约束的表

-- 创建一张班级表 , 班级 id 设置为主键
create table class(
    class_id int primary key auto_increment,
    class_name varchar(20)
);

1.创建表添加外键约束

命令

create table <数据表名>(<列名1> <类型>,<列名2> <类型>,....,foreign key(<外键指定字段>)) references <主数据表> (<指定主表主键>);  

示例

创建一张 student 表 , 并指定 id 列作为外键 , 指向 class 表中的 class_id 主键

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int,
    sn int,
    name varchar(20),
    qq_mail varchar(20),
    foreign key(id) references class(class_id)
);

查看两张表结构 , 在 class 表中 class_id 字段中的 Key 的状态为主键 , 在 student 表中 id 字段中的状态为 MUL , 表示是外键约束

-- 查看 class 表结构
mysql> desc class;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| class_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| class_name | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 查看 student 表结构
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | MUL | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

class 表为记录为空的情况下,student 表此时添加数据会报错

insert into student values(1,2001,'张三','123@qq.com');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`))

-- error : 无法添加或更新子行:外键约束失败(' test ')。' student ', CONSTRAINT ' student_ibfk_1 ' FOREIGN KEY (' id ') REFERENCES ' class ' (' class_id '))

正确的添加记录 , 是需要先给 class 主表添加记录,因为 class 表带自增主键,id 列可以为 null , 只有在 class 表中有记录之后 , student 中才能进行添加记录。

-- class 表添加两条记录
insert into class values(null,'1班');
Query OK, 1 row affected (0.00 sec)

insert into class values(null,'2班');
Query OK, 1 row affected (0.00 sec)

-- student 表添加三条记录
insert into student values(1,2001,'张三','123@qq.com');
Query OK, 1 row affected (0.00 sec)

insert into student values(2,2001,'李四','123@qq.com');
Query OK, 1 row affected (0.00 sec)

insert into student values(1,2001,'王五','123@qq.com');
Query OK, 1 row affected (0.00 sec)

不过需要注意 , 在 student 表中添加数据中 id 列 的值必须符合主表 classclass_id 中的值 , 由于在 class 表当中,class_Id 列只有 1 和 2 , 此时 student 表添加 id 为 3 的记录,由于外键约束失败导致插入失败

-- student 表添加数据 , id 列的值不符合 class_id 的值 , 所以会添加失败
insert into student values(3,2001,'赵六','123@qq.com');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`))
-- 无法添加或更新子行:外键约束失败(' test ')。' student ', CONSTRAINT ' student_ibfk_1 ' FOREIGN KEY (' id ') REFERENCES ' class ' (' class_id '))

如果此时去删除或者修改 class 表中的记录 , 会发生什么? 依旧会报错 , 一旦受约束表当中存入了当前约束表当中的值,那么此时约束表的记录就不能轻易的去删除或者修改 ! !

-- 修改 class 表中的 class_id 的值
update class set class_id = 3 where class_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`))
-- error : 无法删除或更新父行:外键约束失败(' test ')。' student ', CONSTRAINT ' student_ibfk_1 ' FOREIGN KEY (' id ') REFERENCES ' class ' (' class_id '))

外键约束带来的效果是 :

  1. 往 student 表中添加的记录, id 列的值必须在 class 表中的 class_id 列存在~

  2. student 表指定的这个外键约束,必须是 class 表的 主键/唯一键

  3. 外键约束建立好了之后,此时 class 表中的 class_id 就不能随便改了

2.在现有表中添加外键约束

在这里重新设置一下 student 表结构 , 让 student 表不添加任何约束

-- 重新设置学生表结构
drop table if exists student;

-- 创建 student 数据表
create table student (
    id int,
    sn int,
    name varchar(20),
    qq_mail varchar(20)
);

-- 查看 student 表结构
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

命令

alter table <数据表名> add foreign key(<外键指定字段>) references <主数据表> (<指定主表主键>);

示例

  • 在现有 student 表中 , 给 id 列添加外键约束 , 关联 class 表中的 class_id

    alter table student add foreign key(id) references class(class_id);

    查看 student 表结构 , 可以看到 , id 字段中的 Key 选项状态已经改成了 MUL 主键约束的状态了

    mysql> desc student;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(11)     | YES  | MUL | NULL    |       |
    | sn      | int(11)     | YES  |     | NULL    |       |
    | name    | varchar(20) | YES  |     | NULL    |       |
    | qq_mail | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

    注意 : 如果在修改时 , student 表中预修改字段中已有数据 , 且绑定外键约束的主表中的主键字段没有该数据 , 会修改失败

3.删除外键约束

删除外键约束的方法和删除唯一约束的方法差不多 , 需要先在表中先找到该外键约束的约束名称 , 通过约束名称解除外键链接 , 在通过键名取消外键状态

命令

alter table <数据表名> drop foreign key <约束名> , drop index <对应的键名>;

查看约束对应的约束名称

show create table <数据表名>;

示例

删除 student 表中的外键约束

  1. 先查看 student 表中所有约束信息 , 找到 id 字段对应的约束名和键名

    show create table student;

    student 表对应的以下一个外键约束 , `` 引起的 student_ibfk_1就是约束名 , KEY 后面反引号引起的 'id' 是键名 

    KEY `id` (`id`),
    CONSTRAINT `student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`);
  2. 使用对应的键名解除 student 表中外键约束 , 在使用对应的键名删除 Key 状态

    alter table student drop foreign key student_ibfk_1 , drop index id;
  3. 查看表结构 , 可以看到虽然 id 字段中的 Key 选项仍然为空了, 此时的 class 表中是没有数据的 , 向 student 表中插入数据 , 是可以进行插入的 , 这就证明 student 表中的外键约束解除了 , 不受到 class 表的影响。

    mysql> desc student;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(11)     | YES  |     | NULL    |       |
    | sn      | int(11)     | YES  |     | NULL    |       |
    | name    | varchar(20) | YES  |     | NULL    |       |
    | qq_mail | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    -- 查看 class 表中的数据
    select * from class;
    Empty set (0.00 sec)
    
    -- 对 student 表添加记录
    insert into student values(1,2001,'张三','123@qq.com');
    Query OK, 1 row affected (0.00 sec)
    
    insert into student values(2,2001,'李四','123@qq.com');
    Query OK, 1 row affected (0.00 sec)
    
    -- 查看 student 表中的数据
    select * from student;
    +------+------+--------+------------+
    | id   | sn   | name   | qq_mail    |
    +------+------+--------+------------+
    |    1 | 2001 | 张三   | 123@qq.com |
    |    2 | 2001 | 李四   | 123@qq.com |
    +------+------+--------+------------+
    2 rows in set (0.00 sec)

 本章到此结束,如果文中有写的不对或不懂的地方,欢迎评论区讨论,谢谢!


点击全文阅读


本文链接:http://zhangshiyu.com/post/38852.html

约束  添加  数据表  
<< 上一篇 下一篇 >>

  • 评论(0)
  • 赞助本站

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

关于我们 | 我要投稿 | 免责申明

Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1