# 约束

# 1 为什么需要约束

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:

实体完整性（Entity Integrity）例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）例如:年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如:员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如:用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

# 2 什么是约束

约束是表级的强制规定。可以在创建表时规定约束(通过 CREATE TABLE 语句)，或者在表创建之后通过 ALTER TABLE 语句规定约束。

# 3 约束的分类

根据约束数据列的限制
- 单列约束: 每个约束只约束一列
- 多列约束: 每个约束可约束多列数据
根据约束的作用范围
- 列级约束: 只能作用在一个列上，跟在列的定义后面
- 表级约束: 可以作用在多个列上，不与列一起，而是单独定义
根据约束起的作用
- NOT NULL 非空约束，规定某个字段不能为空
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- CHECK 检查约束
- DEFAULT 默认值约束

查看某个表已有的约束

#information_schema数据库名(系统库) #table_constraints表名称(专门存储各个表的约束)
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

1
2

# 4 非空约束 NOT NULL

添加非空约束

-- 建表时
CREATE TABLE 表名称( 字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL );

-- 建表后
alter table student modify sname varchar(20) not null;

1
2
3
4
5
6
7

删除非空约束

ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

# 5 唯一性约束 UNIQUE

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引。

添加唯一约束

-- 建表时
CREATE TABLE t_course(
    cid INT UNIQUE,
    cname VARCHAR(100) UNIQUE,
    description VARCHAR(200)
);

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 建表后
 ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);
 
 ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

1
2
3
4
5
6

删除唯一约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同;如果是组合列，那么默认和() 中排在第一个的列名相同。也可以自定义唯一性约束名。

-- 查看都有哪 些约束
 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; 
-- 查看表的索引
show index from 表名称
-- 删除索引
 ALTER TABLE USER
DROP INDEX uk_name_pwd;

1
2
3
4
5
6
7

# 6 主键约束

建表时指定主键约束

create table temp(
    id int primary key,
    name varchar(20)
);

create table student_course(
sid int,
cid int,
score int,
primary key(sid,cid) #复合主键
);

CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

建表后增加主键约束

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

删除主键约束

ALTER TABLE student DROP PRIMARY KEY;

# 7 自增列:AUTO_INCREMENT

建表时

create table employee(
    eid int primary key auto_increment,
    ename varchar(20)
);

1
2
3
4

建表后

  alter table employee modify eid int auto_increment;

删除自增约束

  alter table employee modify eid int;

# 8 外键 FOREIGN KEY 约束

限定某个表的某个字段的引用完整性。比如:员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

阿里开发规范

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

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

建表时添加外键约束

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) #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明: (1)主表dept必须先创建成功，然后才能创建emp表，指定外键成功。 (2)删除表时，先删除从表emp，再删除主表dept

1
2
3
4
5
6
7
8
9
10

建表后

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];

alter table emp add foreign key (deptid) references dept(did);

1
2
3

删除外键约束

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个 表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名; 

(2)第二步查看索引名和删除索引。(注意，只能手动删除)
SHOW INDEX FROM 表名称; #查看某个表的索引名 

ALTER TABLE 从表名 DROP INDEX 索引名;

1
2
3
4
5
6
7
8
9

# 约束等级

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: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(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门 
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);

#修改主表成功，从表也跟着修改，修改了主表被引用的字段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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

演示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等级，把删除操作设置为级联删除等级
);

1
2
3
4
5
6
7
8
9

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);

1
2
3
4
5
6
7

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#修改主表，从表对应的字段设置为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  |
| 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#删除主表的记录成功，主表的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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

演示: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(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门 insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);

1
2
3
4
5
6
7
8
9
10
11
12
13

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)

1
2
3
4
5
6
7
8
9
10

#修改主表，从表对应的字段自动修改
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

#删除主表的记录成功，主表的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)

1
2
3
4
5
6
7
8
9
10
11

# 9.CHECK 约束

检查某个字段的值是否符号xx要求，一般指的是值的范围,MySQL 5.7 不支持

1.建表时

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

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))
CHECK(height>=0 AND height<3)

1
2
3
4
5
6
7
8

# 10.DEFAULT约束

建表时

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

1
2
3
4
5
6

建表后

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

1
2

删除默认值约束

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

1
2

← 索引存储过程与自定义函数 →