6. 7 约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子

表的外键列不能为not null

No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作

Restrict方式：同no action，都是立即检查外键约束

Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

（ 1 ）演示 1 ：on update cascade on delete set null

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);

mysql> select * from dept;
mysql> select * from emp;

#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 | #原来是1002，修改为1004
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1004 | #原来是1002，跟着修改为1004
+-----+-------+--------+
3 rows in set (0.00 sec)

#删除主表的记录成功，从表对应的字段的值被修改为null
mysql> delete from dept where did = 1001;
Query OK, 1 row affected (0.01 sec)
mysql> select * from dept;
+------+--------+
| did | dname | #记录1001部门被删除了
+------+--------+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | NULL | #原来引用1001部门的员工，deptid字段变为null
| 2 | 李四 | NULL |
| 3 | 王五 | 1004 |
+-----+-------+--------+
3 rows in set (0.00 sec)

（ 2 ）演示 2 ：on update set null on delete cascade

create table dept(
did int primary key, #部门编号
dname varchar( 50 ) #部门名称
);
create table emp(
eid int primary key,  #员工编号
ename varchar( 5 ), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did)  on update set null on delete cascade
#把修改操作设置为set null等级，把删除操作设置为级联删除等级
);

insert into dept values( 1001 ,'教学部');
insert into dept values( 1002 , '财务部');
insert into dept values( 1003 , '咨询部');
insert into emp values( 1 ,'张三', 1001 ); #在添加这条记录时，要求部门表有 1001 部门
insert into emp values( 2 ,'李四', 1001 );
insert into emp values( 3 ,'王五', 1002 );

mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1002 | 财务部 |
| 1003 | 咨询部 |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1002 |
+-----+-------+--------+
3 rows in set (0.00 sec)

#修改主表，从表对应的字段设置为null
mysql> update dept set did = 1004 where did = 1002 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 | #原来did是1002
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | NULL | #原来deptid是1002，因为部门表1002被修改了，1002没有对应的了，就设置为
null
+-----+-------+--------+
3 rows in set (0.00 sec)

#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+------+--------+
| did | dname | #部门表中1001部门被删除
+------+--------+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |#原来1001部门的员工也被删除了
+-----+-------+--------+
| 3 | 王五 | NULL |
+-----+-------+--------+
1 row in set (0.00 sec)

（ 3 ）演示：on update cascade on delete cascade

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete cascade
#把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
);

insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);

mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1002 | 财务部 |
| 1003 | 咨询部 |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1002 |
+-----+-------+--------+
3 rows in set (0.00 sec)

#修改主表，从表对应的字段自动修改
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 | #部门1002修改为1004
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1004 | #级联修改
+-----+-------+--------+
3 rows in set (0.00 sec)

#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+------+--------+
| did | dname | #1001部门被删除了
+------+--------+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid | #1001部门的员工也被删除了
+-----+-------+--------+
| 3 | 王五 | 1004 |
+-----+-------+--------+
1 row in set (0.00 sec)

8 删除外键约束
流程如下：

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个
表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

举例：

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from emp;
mysql> alter table emp drop index deptid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from emp;

6. 9 开发场景

问题 1 ：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题 2 ：建和不建外键约束有什么区别？

答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

问题 3 ：那么建和不建外键约束和查询有没有关系？

答：没有

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不

用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

6. 10 阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

7. CHECK 约束

7. 1 作用

检查某个字段的值是否符号xx要求，一般指的是值的范围

7.2 、关键字

CHECK

7.3 、说明：MySQL 5. 7 不支持

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告

但是 MySQL 8.0中可以使用check约束了。

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);

insert into employee values(1,'张三','妖');

mysql> select * from employee;
+-----+-------+--------+
| eid | ename | gender |
+-----+-------+--------+
| 1 | 张三 | 妖 |
+-----+-------+--------+
1 row in set (0.00 sec)

再举例

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

再举例

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

再举例

CHECK(height>=0 AND height<3)

8. DEFAULT约束

8. 1 作用

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

8. 2 关键字

DEFAULT

8. 3 如何给字段加默认值

（ 1 ）建表时

create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,`在这里插入代码片`
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
说明：默认值约束一般不在唯一键和主键列上加

create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | char(1) | YES | | 男 | |
| tel | char(11) | NO | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

insert into employee values(1,'汪飞','男','13700102535'); #成功

mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel |
+-----+-------+--------+-------------+
| 1 | 汪飞 | 男 | 13700102535 |
+-----+-------+--------+-------------+
1 row in set (0.00 sec)

insert into employee(eid,ename) values(2,'天琪'); #成功

mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel |
+-----+-------+--------+-------------+
| 1 | 汪飞 | 男 | 13700102535 |
| 2 | 天琪 | 男 | |
+-----+-------+--------+-------------+
2 rows in set (0.00 sec)

insert into employee(eid,ename) values(3,'二虎');
#ERROR 1062 (23000): Duplicate entry '' for key 'tel'
#如果tel有唯一性约束的话会报错，如果tel没有唯一性约束，可以添加成功

再举例：

CREATE TABLE myemp(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2000
);

（ 2 ）建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被
删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默
认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

create table employee(
eid int primary key,
ename varchar(20),
gender char,
tel char(11) not null
);

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| tel | char(11) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

alter table employee modify gender char default '男'; #给gender字段增加默认值约束
alter table employee modify tel char(11) default ''; #给tel字段增加默认值约束

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | 男 | |
| tel | char(11) | YES | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

alter table employee modify tel char(11) default '' not null;#给tel字段增加默认值约束，并
保留非空约束

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | 男 | |
| tel | char(11) | NO | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

8. 4 如何删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| tel | char(11) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

9. 面试

面试 1 、为什么建表时，加 not null default ‘’ 或 default 0

答：不想让表中出现null值。

面试 2 、为什么不想要 null 的值

答:（ 1 ）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。

（ 2 ）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

面试 3 、带AUTO_INCREMENT约束的字段值是从 1 开始的吗？

在MySQL中，默认AUTO_INCREMENT的初始值是 1 ，每新增一条记录，字段值自动加 1 。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为 5 ，则以后插入的记录的id值就会从 6 开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

面试 4 、并不是每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

MySQL数据库，从入门到精通：第十三篇——MySQL数据表约束详解（四）

6. 7 约束等级

6. 9 开发场景

6. 10 阿里开发规范

7. CHECK 约束

7. 1 作用

7.2 、关键字

7.3 、说明：MySQL 5. 7 不支持

8. DEFAULT约束

8. 1 作用

8. 2 关键字

8. 3 如何给字段加默认值

8. 4 如何删除默认值约束

9. 面试

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像