约束( constraint ) 概述
约束是对表中字段的限制
数据完整性
为了保证数据完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
- 实体完整性：同一个表中，不存在两条完全相同无法区分的记录
- 域完整性：字段的取值范围，如年龄1-150
- 引用完整性：员工表中的员工所在部门，可以在部门表中找到该部门
- 用户自定义完整性：用户名唯一、密码不为空等
约束的分类
约束字段的个数
约束的作用范围
- 列级约束：将此约束声明在对应字段后面
- 表级约束：在所有字段后面声明的约束
约束的功能
NOT NULL（非空约束）UNIQUE（唯一性约束）PRIMARY KEY（主键约束）FOREIGN KEY（外键约束）CHECK（检查约束）DEFAULT（默认值约束）
如何查看表中约束
1
2
3
4
5
6
7
8
9
10
11
12
| SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+
| def | atguigudb | emp_email_uk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | emp_emp_id_pk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | PRIMARY | atguigudb | employees | PRIMARY KEY | YES |
| def | atguigudb | emp_dept_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_job_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_manager_fk | atguigudb | employees | FOREIGN KEY | YES |
+
|
约束可以在CREATE TABLE或者ALTER TABLE时添加
MySQL中的约束
非空约束
作用
关键字NOT NULL，限定某个字段的值不允许为空
添加约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| CREATE TABLE test1 (
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);
DESC test1;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | int | NO | | NULL | |
| last_name | varchar(15) | NO | | NULL | |
| email | varchar(25) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+
|
1
2
| ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
|
唯一性约束
作用
关键字UNIQUE，限制某个字段的值不能重复
添加约束
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
| CREATE TABLE test2 (
id INT UNIQUE, # 列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
# 表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);
DESC test2;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | int | YES | UNI | NULL | |
| last_name | varchar(15) | YES | | NULL | |
| email | varchar(25) | YES | UNI | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';
+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+
| def | dbtest14 | id | dbtest14 | test2 | UNIQUE | YES |
| def | dbtest14 | uk_test2_email | dbtest14 | test2 | UNIQUE | YES |
+
|
1
2
3
4
5
6
7
| # 添加表级约束
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal_name UNIQUE(salary, last_name); # 复合的唯一性约束
# 添加列级约束
ALTER TABLE test2
MODIFY salary DECIMAL(10, 2) UNIQUE;
|
注意：
- 在创建唯一约束时，若不给唯一约束命名，默认和列名相同（复合唯一约束与第一列相同）
- 添加唯一性约束的列会自动创建唯一索引
删除唯一性约束
删除唯一性约束只能通过删除唯一索引的方式删除，唯一索引名和唯一约束名相同
1
2
| ALTER TABLE test2
DROP INDEX uk_test2_sal_name; # 删除唯一索引
|
主键约束
作用
关键字PRIMARY KEY，用来唯一标识表中的一行记录
添加约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| CREATE TABLE test3 (
id INT PRIMARY KEY, # 列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2)
);
CREATE TABLE test4 (
id INT,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
# 表级约束
PRIMARY KEY(id, last_name) # 没有必要起名字
);
|
自增列
作用
关键字AUTO_INCREMENT，实现某个字段的值自增
- 自增默认从1开始，从最大值开始自增
- 一个表最多有一个自增列
- 自增约束的列必须是键列
- 自增约束的列数据类型必须是整型的
添加约束
1
2
3
4
| CREATE TABLE test5(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
|
外键约束
作用
关键字FOREIGN KEY，限定某个表某个字段的引用完整性
- 从表的外键列，必须引用主表的主键或者唯一约束列
- 创建外键约束时，默认约束名不是列名，而是自动产生一个外键名
- 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。索引名是列名，不是外键约束名
- 删除外键约束后，必须手动删除对应索引
添加约束
先创建主表
1
2
3
4
| CREATE TABLE dept1 (
dept_id INT PRIMARY KEY, # 主键
dept_name VARCHAR(15)
);
|
再创建从表
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
| CREATE TABLE emp1 (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
# 表级约束，添加外键约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
DESC emp1;
+
| Field | Type | Null | Key | Default | Extra |
+
| emp_id | int | NO | PRI | NULL | auto_increment |
| emp_name | varchar(15) | YES | | NULL | |
| department_id | int | YES | MUL | NULL | |
+
SELECT *
FROM information_schema.table_constraints
WHERE table_name = 'emp1';
+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+
| def | dbtest14 | PRIMARY | dbtest14 | emp1 | PRIMARY KEY | YES |
| def | dbtest14 | fk_emp1_dept_id | dbtest14 | emp1 | FOREIGN KEY | YES |
+
|
外键约束等级
CASCADE方式：在父表中UPDATE/DELETE记录时，同步UPDATE/DELETE子表的匹配记录SET NULL方式：在父表中UPDATE/DELETE记录时，在子表上匹配的记录的字段设为NULLNO ACTION方式：如果子表中有匹配的记录，则不允许对父表对应的列进行UPDATE/DELETE操作RESTRICT方式：同NO ACTIONSET DEFAULT方式：父表更新时，子表将外键设置为一个默认的值，但innodb不能识别
对于外键约束，最好采用：ON UPDATE CASCADE ON DELETE RESTRICT的方式
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE TABLE dept(
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(25),
department_id INT,
# 设置外键
CONSTRAINT fk_emp_dept_id FOREIGN KEY (department_id) REFERENCES dept(department_id) ON UPDATE CASCADE ON DELETE RESTRICT
);
|
删除约束
1
2
| ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
|
1
2
| ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id; # 利用外键约束名删除
|
因为外键约束的开销，会使系统变得非常慢，建议在应用层面检查数据一致性
检查约束
作用
关键字CHECK，检查某个字段的值是否符合要求（5.7版本不起作用，8.0支持）
添加约束
1
2
3
4
5
6
| CREATE TABLE test6 (
id INT,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2) CHECK (salary > 2000)
);
|
默认值约束
作用
关键字DEFAULT，插入数据时若没有显式赋值，则设置为默认值
添加约束
1
2
3
4
5
6
| CREATE TABLE test7 (
id INT,
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2) DEFAULT 2000
);
|