封面来源：本文封面来源于 MySQL 官网，如有侵权，请联系删除。

本文参考：MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！

1. 约束的概述

1.1 为什么需要约束

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

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

1、实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录；

2、域完整性（Domain Integrity）：例如，年龄范围 0 到 150，性别的普遍范围是男或女；

3、引用完整性（Referential Integrity）：例如，员工所在部门，在部门表中要能找到这个部门；

4、用户自定义完整性（User-defined Integrity）：例如，用户名唯一、密码不能为空、本部门经理的工资不得高于本部门职工的平均工资的 5 倍等。

1.2 什么是约束

约束（constraint）是表级的强制规定。

约束可以在创建表时通过 CREATE TABLE 语句规定，或者在表创建之后通过 ALTER TABLE 语句规定。

1.3 约束的分类

根据约束数据列的限制，可分为：

  • 单列约束：每个约束只约束一列；

  • 多列约束：每个约束可约束多列数据。

根据约束的作用范围，可分为：

  • 列级约束：只能作用在一个列上，跟在列的定义后面；

  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义。

位置 支持的约束类型 是否可以起约束名
列级约束 列的后面 语法都支持，但外键没有效果 不可以
表级约束 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）

根据约束起的作用，可分为：

  • NOT NULL：非空约束，规定某个字段不能为空；

  • UNIQUE：唯一约束，规定某个字段在整个表中是唯一的；

  • PRIMARY KEY：主键（即非空且唯一）约束；

  • FOREIGN KEY：外键约束；

  • CHECK：检查约束；

  • DEFAULT：默认值约束。

注意： MySQL 不支持 check 约束，虽然可以使用 check 约束，而没有效果。

查看某个表已有的约束

1
2
3
# information_schema：数据库名（系统库）
# table_constraints：表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = '表名称';

2. 非空约束

2.1 非空约束概述

非空约束限定某个字段或者说某列的值不能为空，使用关键字 NOT NULL 实现非空约束。非空约束有以下特点：

  • 所有的类型的值都可以是 NULL，包括 INT、FLOAT 等数据类型；

  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空；

  • 一个表可以有多个列被设置非空约束；

  • 空字符串不等于 NULL，0 也不等于 NULL。

2.2 添加非空约束

创建表时添加非空约束

语法：

1
2
3
4
5
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型 NOT NULL,
    字段名  数据类型 NOT NULL
);

比如：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE emp(
	id INT(10) NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	sex CHAR NULL
);

CREATE TABLE student(
	sid INT,
	sname VARCHAR(20) NOT NULL,
	tel CHAR(11) ,
	cardid CHAR(18) NOT NULL
);

尝试插入数据：

1
2
3
4
5
6
7
8
9
INSERT INTO student VALUES(1, '张三', '13710011002', '110222198912032545');

# 错误：Column 'cardid' cannot be null
INSERT INTO student VALUES(2, '李四', '13710011002', null);

INSERT INTO student VALUES(2, '李四', null, '110222198912032546');

# 错误：Column 'sname' cannot be null
INSERT INTO student VALUES(3, null, null, '110222198912032547');

创建表后添加非空约束

语法：

1
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

比如：

1
2
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;
ALTER TABLE student MODIFY sname varchar(20) NOT NULL;

2.3 删除非空约束

语法：

1
2
3
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
# 或
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

比如：

1
2
ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;
ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3. 唯一性约束

3.1 唯一性约束概述

唯一性约束用来限制某个字段或者说某列的值不能重复，使用关键字 UNIQUE 实现唯一性约束。唯一性约束有以下特点：

  • 同一个表可以有多个唯一约束；

  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一；

  • 唯一性约束允许列值为空（NULL）；

  • 在创建唯一约束的时候，如果不给唯一约束命名，其名称默认和列名相同；

  • MySQL 会给使用了唯一约束的列上默认创建一个唯一索引。

3.2 添加唯一约束

创建表时添加唯一约束

语法：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型  UNIQUE,  
    字段名  数据类型  UNIQUE KEY,
    字段名  数据类型
);
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [CONSTRAINT 约束名] UNIQUE KEY(字段名)
);

比如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE student(
    sid INT,
    sname VARCHAR(20),
    tel CHAR(11) UNIQUE,
    cardid CHAR(18) UNIQUE KEY
);

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

尝试向 student 表中插入数据：

1
2
3
4
5
6
7
INSERT INTO student VALUES(1, '张三', '13710011002', '101223199012015623');
INSERT INTO student VALUES(2, '李四', '13710011003', '101223199012015624');

# 错误：Duplicate entry '101223199012015624' for key 'cardid'
INSERT INTO student VALUES(3,'王五','13710011004','101223199012015624');
# 错误：Duplicate entry '13710011003' for key 'tel'
INSERT INTO student VALUES(3,'王五','13710011003','101223199012015625');

创建表后添加唯一约束

语法：

1
2
3
4
5
# 字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
# 方式一：
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表); 
# 方式二：
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

比如：

1
2
3
4
ALTER TABLE USER ADD UNIQUE(NAME, PASSWORD);
-- 约束名为 uk_name_pwd
ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);
ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;

再比如：

1
2
3
4
5
6
7
8
9
CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    tel CHAR(11),
    cardid CHAR(18) 
);

ALTER TABLE student ADD UNIQUE KEY(tel);
ALTER TABLE student ADD UNIQUE KEY(cardid);

3.3 复合唯一约束

语法：

1
2
3
4
5
6
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    unique key(字段列表) # 字段列表中包含多个字段名，彼此逗号分隔，表示复合唯一，即多个字段值是组合唯一的
);

比如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 学生表
CREATE TABLE student(
    sid INT,
    sname VARCHAR(20),
    tel CHAR(11) UNIQUE KEY,
    cardid CHAR(18) UNIQUE KEY
);

# 课程表
CREATE TABLE course(
    cid INT,
    cname VARCHAR(20)
);

# 选课表
CREATE TABLE student_course(
    id INT,
    sid INT,
    cid INT,
    score INT,
    UNIQUE KEY(sid,cid)
);

尝试插入数据：

1
2
3
4
5
INSERT INTO student VALUES(1, '张三', '13710011002', '101223199012015623');
INSERT INTO student VALUES(2, '李四', '13710011003', '101223199012015624');
INSERT INTO course VALUES(1001, 'Java'), (1002, 'MySQL');
INSERT INTO student_course VALUES (1, 1, 1001, 89), (2, 1, 1002, 90),
(3, 2, 1001, 88), (4, 2, 1002, 56);

插入重复的数据：

1
2
# 出错：Duplicate entry '1-1001' for key 'sid'
INSERT INTO student_course VALUES(5, 1, 1001, 88);

3.4 删除唯一约束

添加唯一约束的列会自动创建唯一索引，删除唯一约束只能通过删除唯一索引的方式进行删除。

删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

如果创建唯一约束时未指定名称且是单列，约束名默认和列名相同；如果是组合列，默认和字段列表中排第一的列名相同，当然也可以自定义唯一性约束名。

1
2
3
4
5
6
# 查看都有哪些约束
SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = '表名';
# 删除 uk_name_pwd 索引
ALTER TABLE USER DROP INDEX uk_name_pwd;
# 查看某个表的索引
SHOW INDEX FROM 表名;

4. 主键约束

4.1 主键约束概述

主键约束是表中某一行记录的唯一标识，使用关键字 PRIMARY KEY 实现主键约束。主键约束有以下特点：

  • 主键约束相当于唯一约束与非空约束的组合，主键约束列既不允许重复，也不允许出现空值；
  • 一个表最多只能有一个主键约束，主键约束的建立可以是在列级别，也可以是在表级别；
  • 主键约束对应着表中的一列或者多列（复合主键），如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复；
  • MySQL的主键名总是 PRIMARY，就算自定义了主键约束名也不会生效；
  • 创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（查询数据时，如果可以根据主键进行查询，就尽量使用主键进行查询，那样效率更高）；删除主键约束时，对应的索引也会自动删除；
  • 注意，不要修改主键字段的值。因为主键是数据记录的唯一标识，当它发生修改时，很可能会破坏数据的完整性。

4.2 添加主键约束

创建表时添加主键约束

语法：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE 表名称(
    字段名  数据类型  PRIMARY KEY, # 列级模式
    字段名  数据类型,
    字段名  数据类型
);
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型,
    字段名  数据类型,
    [CONSTRAINT 约束名] PRIMARY KEY(字段名) # 表级模式
);

比如：

1
2
3
4
CREATE TABLE temp(
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

temp的表结构

依次插入多条数据：

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO temp VALUES(1, '张三');
INSERT INTO temp VALUES(2, '李四');
# 错误：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO temp VALUES(1, '张三');
# 错误：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO temp VALUES(1, '王五');
# 成功
INSERT INTO temp VALUES(3, '张三');
# 成功
INSERT INTO temp VALUES(4, NULL);
# 错误：Column 'id' cannot be null
INSERT INTO temp VALUES(null, '默烦');

演示创建拥有两个主键约束的表：

1
2
3
4
5
# 错误：Multiple primary key defined
CREATE TABLE temp(
	id INT PRIMARY KEY,
	name VARCHAR(20) PRIMARY KEY
);

列级主键约束：

1
2
3
4
CREATE TABLE emp4(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20)
);

表级主键约束：

1
2
3
4
5
6
CREATE TABLE emp5(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20),
    pwd VARCHAR(15),
    CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

创建表后添加主键约束

语法：

1
2
# 字段列表可以是一个字段，也可以是多个字段，如果是多个字段，表示复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

比如：

1
2
ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME, pwd);

4.3 复合主键约束

语法：

1
2
3
4
5
6
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    PRIMARY KEY(字段名1, 字段名2)  # 字段 1 和字段 2 组合唯一
);

比如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 学生表
CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(20)
);

# 课程表
CREATE TABLE course(
    cid INT PRIMARY KEY,
    cname VARCHAR(20)
);

# 选课表
CREATE TABLE student_course(
    sid INT,
    cid INT,
    score INT,
    PRIMARY KEY(sid, cid)
);

尝试插入数据：

1
2
3
4
INSERT INTO student VALUES(1, '张三'), (2, '李四');
INSERT INTO course VALUES(1001, 'Java'), (1002, 'MySQL');
INSERT INTO student_course VALUES(1, 1001, 89), (1,1002,90),
(2, 1001, 88), (2, 1002, 56);

插入重复的数据：

1
2
# 错误：Duplicate entry '1-1001' for key 'PRIMARY'
INSERT INTO student_course VALUES(1, 1001, 100);

4.4 删除主键约束

语法：

1
ALTER TABLE 表名称 DROP PRIMARY KEY;

比如：

1
2
ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE emp5 DROP PRIMARY KEY;

上述第二条示例语句在运行时会报错，错误如下：

Incorrect table definition; there can be only one auto column and it must be defined as a key

创建 emp5 表时指定 id 列为自动递增，id 列必须是键列，因此不能删除 emp5 表的主键（下文会讲）。

由于一个表只有一个主键，因此在删除主键约束时不需要指定主键名，成功删除主键约束后，非空约束依旧存在。

5. 自增列

5.1 自增列概述

使用 AUTO_INCREMENT 关键字可以使某个字段的值自增，其特点与要求如下：

  • 一个表最多只能有一个自增长列；
  • 当需要产生唯一标识符或顺序值时，可以设置自增长；
  • 自增长列约束的列 必须 是键列（主键列，唯一键列）；
  • 自增约束的列的数据类型 必须 是整数类型；
  • 插入数据时，如果给自增列指定了 0 和 NULL，自增列的值不会被设置为 0 或 NULL，而是会在当前最大值的基础上自增；如果手动指定了其他具体值，自增列的值会被赋值为指定的具体值。

错误的示范

1
2
3
4
5
6
7
8
9
10
11
# 错误：Incorrect table definition; there can be only one auto column and it must be defined as a key
CREATE TABLE employee(
    eid INT AUTO_INCREMENT,
    ename VARCHAR(20)
);

# 错误：Incorrect column specifier for column 'ename'
CREATE TABLE employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(20) UNIQUE KEY AUTO_INCREMENT
);

5.2 指定自增约束

创建表时指定自增约束

语法：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE 表名称(
    字段名  数据类型  PRIMARY KEY AUTO_INCREMENT,
    字段名  数据类型  UNIQUE KEY NOT NULL,  
    字段名  数据类型  UNIQUE KEY,
    字段名  数据类型  NOT NULL DEFAULT 默认值, 
);
CREATE TABLE 表名称(
    字段名  数据类型 DEFAULT 默认值 ,
    字段名  数据类型 UNIQUE KEY AUTO_INCREMENT,  
    字段名  数据类型 NOT NULL DEFAULT 默认值,
    PRIMARY KEY(字段名)
);

比如：

1
2
3
4
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20)
);

employee的表结构

创建表后指定自增约束

语法：

1
ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;

比如：

1
2
3
4
5
6
create table employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(20)
);

ALTER TABLE employee MODIFY eid INT AUTO_INCREMENT;

5.3 删除自增约束

语法：

1
2
# 去掉末尾的 AUTO_INCREMENT 就相当于删除自增约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

比如：

1
ALTER TABLE employee MODIFY eid int;

5.4 MySQL 8.0 新特性 — 自增变量的持久化

在 MySQL 8.0 之前，自增主键 AUTO_INCREMENT 的值如果大于 max(primary key) + 1，在 MySQL 重启后，会重置 AUTO_INCREMENT = max(primary key) + 1，这种现象在某些情况下会导致业务主键冲突或其他难以发现的问题。

下面通过案例来对比不同的版本中自增变量是否持久化。 在 MySQL 5.7 版本中，创建的数据表中包含自增主键的 id 字段，语句如下：

1
2
3
CREATE TABLE test1(
    id INT PRIMARY KEY AUTO_INCREMENT
);

然后插入 4 个 0：

1
INSERT INTO test1 VALUES(0), (0), (0), (0);

查询 test1 表中的数据，可以看到 id 从 1 到 4 共 4 条数据。

删除 id 为 4 的数据：

1
DELETE FROM test1 WHERE id = 4;

再次插入 0：

1
INSERT INTO test1 VALUES(0);

再次查询 test1 表中的数据：

test1表中的数据

虽然删除了 id 为 4 的数据，但是再次插入 0 时，并没有重用被删除的 4，而是分配到了 5。删除 id 为 5 的数据，然后 重启数据库， 并再次插入 0：

1
2
3
DELETE FROM test1 WHERE id = 5;
# 重启数据库
INSERT INTO test1 VALUES(0);

再次查询数据库 test1 中的数据，会发现新插入的 0 值被分配到了 4，按照重启前的操作逻辑，此处应该分配到 6。出现这种情况的原因是在 MySQL 5.7 中的自增主键没有持久化。 在 MySQL 5.7 中，自增主键的分配规则是 InnoDB 数据字典内部的一个计数器来决定的，这个计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

在 MySQL 8.0 中，重复以上步骤，最后新插入的 0 值被分配到了 6，这表示在 MySQL 8.0 中自增变量已经被持久化了。

MySQL 8.0 将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB 会根据重做日志中的信息来初始化计数器的内存值。

6. 外键约束

6.1 外键约束概述

外键约束可以限定某个表的某个字段的引用完整性，比如员工表中某一员工所在部门的选择必须在部门表能找到对应的部分。外键约束可以使用 FOREIGN KEY 关键字来实现。

在介绍外键约束的特点前，先明白主表与从表（或者说父表与子表）的含义：

  • 主表（父表）：被引用的表，被参考的表

  • 从表（子表）：引用别人的表，参考别人的表

比如，员工表的员工所在部门这个字段的值要参考部门表，那么部门表是主表，而员工表是从表。

再比如，选课表中的学生列和课程列要分别参考学生表和课程表，那么学生表和课程表是主表，选课表是从表。

简单来说，当一个表依赖了另一个表，那这个表就是从表，而另一个表就是主表。

再回到外键约束的特点，其特点如下：

1、从表的外键列，必须引用（或者说参考）主表的主键或拥有唯一约束的列，因为被依赖（或者说被参考）的值必须是唯一的；

2、在创建外键约束时，如果不给外键约束命名，默认名并不是列名，而是自动产生的一个外键名，例如：student_ibfk_1。

3、如果需要在创建表时就指定外键约束，必须先创建主表，再创建从表；

4、删除表时，需要先删从表（或先删除外键约束），再删除主表；

5、当主表的记录被从表引用时，主表被引用的记录将不允许删除，如果需要删除数据，得先删除从表中依赖该记录的数据，然后才可以删除主表的数据；

6、一个表可以建立多个外键约束；

7、从表的外键列与主表被参照的列彼此的列名可以不相同，但是数据类型必须一样，且逻辑意义一致。

8、当创建外键约束时，系统默认会在所在列上创建对应的普通索引，索引名是外键的约束名（根据外键查询的效率很高）。

9、删除外键约束后，必须 手动 删除对应的索引。

6.2 添加外键约束

创建表时添加外键约束

语法：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE 主表名称(
    字段1  数据类型 PRIMARY KEY,
    字段2  数据类型
);

CREATE TABLE 从表名称(
    字段1  数据类型 PRIMARY KEY,
    字段2  数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY (从表的某个字段) REFERENCES 主表名(被参考字段)
);

比如：

1
2
3
4
5
6
7
8
9
10
11
12
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)
);

主表 dept 必须先创建成功，才能创建 emp，并指定外键成功。删除表时，需要先删除从表 emp 然后才能删除主要 dept。

创建表后添加外键约束

一般来说，在设计时就应该将表与表之间的关系提前设计好，然后在创建表时定义外键约束，但也不是说不能在创建表后再添加外键约束，此时可以使用修改表的方式来补充定义。语法如下：

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

比如：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE dept(
    did INT PRIMARY KEY,
    dname VARCHAR(50)
);

CREATE TABLE emp(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    deptid INT
);

再添加外键：

1
ALTER TABLE emp ADD FOREIGN KEY (deptid) REFERENCES dept(did);

6.3 其他操作示例

引用的列不是主键或拥有唯一索引的列

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dept(
    did INT,
    dname VARCHAR(50)
);

CREATE TABLE emp(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    deptid INT,
    FOREIGN KEY (deptid) REFERENCES dept(did)
);

错误信息：

Failed to add the foreign key constraint. Missing index for constraint 'emp_ibfk_1' in the referenced table 'dept'

数据类型不一致

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dept(
    did INT PRIMARY KEY,
    dname VARCHAR(50)
);

CREATE TABLE emp(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    deptid CHAR,
    FOREIGN KEY (deptid) REFERENCES dept(did)
);

错误信息：

Cannot add foreign key constraint

两个表字段名一样

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dept(
    did INT PRIMARY KEY,
    dname VARCHAR(50)
);

CREATE TABLE emp(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    did INT,
    FOREIGN KEY (did) REFERENCES dept(did)
);

添加外键时，对引用列与被引用列的列名是否相同没有要求，只要它们的类型和逻辑含义相同即可。

数据添加、修改、删除失败

首先创建两个表：

1
2
3
4
5
6
7
8
9
10
11
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)
);

先向主表添加两条数据：

1
2
INSERT INTO dept VALUES(1001, '教学部');
INSERT INTO dept VALUES(1003, '财务部');

再向从表插入数据：

1
2
3
INSERT INTO emp VALUES(1, '张三', 1001);
# 插入失败
INSERT INTO emp VALUES(2,'李四',1005);

向从表插入第二条数据失败，因为主表中没有 1005 部门。错误信息如下：

Cannot add or update a child row: a foreign key constraint fails (`mysql_study`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))

对从表数据进行修改：

1
UPDATE emp SET deptid = 1002 WHERE eid = 1;

从表更新失败，因为主表中没有 1002 部门。错误信息如下：

Cannot add or update a child row: a foreign key constraint fails (`mysql_study`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))

对主表数据进行修改：

1
UPDATE dept SET did = 1002 WHERE did = 1001;

主表更新失败，因为主表中 1001 部门已经被从表使用。错误信息如下：

Cannot delete or update a parent row: a foreign key constraint fails (`mysql_study`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))

对主表数据进行删除：

1
DELETE FROM dept WHERE did=1001;

数据删除失败，1001 部门已经被从表引用。错误信息如下：

Cannot delete or update a parent row: a foreign key constraint fails (`mysql_study`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))

总结

外键约束是针对双方的：

  • 添加了外键约束后，主表的修改和删除数据受到约束

  • 添加了外键约束后，从表的添加和修改数据受到约束

  • 从表外键列所使用的数据要求在主表上必须存在

  • 删除主表数据时，需要先删除从表数据，或将从表中外键引用该主表的关系删除

6.4 约束等级

  • CASCADE：在主表上更新或删除记录时，同步更新或删除从表中匹配的数据；
  • SET NULL：在主表上更新或删除记录时，将从表中匹配记录的列设为 NULL，注意此时从表的外键列不能有非空（NOT NULL）约束；
  • NO ACTION：如果从表内有匹配的记录，则不允许对主表对应候选键进行更新或删除操作；
  • RESTRICT：同 NO ACTION，都是立即检查外键约束；
  • SET DEFAULT（在可视化工具中可能显示空白）：主表有变更时，从表将外键列设置为一个默认值，但 InnoDB 不能识别。

如果没有显式指定约束等级，默认是 RESTRICT 级别。

对于外键约束，最好是在更新数据时采用 CASCADE，在删除数据时采用 RESTRICT。

演示一：ON UPDATE CASCADE ON DELETE SET NULL

先创建表：

1
2
3
4
5
6
7
8
9
10
11
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
);

再分别插入数据：

1
2
3
4
5
6
7
INSERT INTO dept VALUES(1001, '教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');

INSERT INTO emp VALUES(1, '张三', 1001);
INSERT INTO emp VALUES(2, '李四', 1001);
INSERT INTO emp VALUES(3, '王五', 1002);

然后对主表数据进行修改，然后再查询主表与从表的数据：

1
2
3
4
UPDATE dept SET did = 1004 WHERE did = 1002;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中，原 did 为 1002 的记录被修改为 1004，在从表中 eid 为 3 对应的记录的 deptid 也被修改为 1004。

尝试对主表数据进行删除，然后再查询主表与从表的数据：

1
2
3
4
DELETE FROM dept WHERE did = 1001;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中 did 为 1001 的记录被删除，在 emp 表中，引用了 dept 表中 did 为 1001 的记录的 deptid 都被设置为 NULL，即 eid 为 1 和 2 的两条记录。

演示二：ON UPDATE SET NULL ON DELETE CASCADE

先建表：

1
2
3
4
5
6
7
8
9
10
11
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
);

分别插入数据：

1
2
3
4
5
6
7
INSERT INTO dept VALUES(1001, '教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');

INSERT INTO emp VALUES(1, '张三', 1001);
INSERT INTO emp VALUES(2, '李四', 1001);
INSERT INTO emp VALUES(3, '王五', 1002);

对主表数据进行修改，然后再查询主表与从表的数据：

1
2
3
4
UPDATE dept SET did = 1004 WHERE did = 1002;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中，原 did 为 1002 的记录被修改为 1004，在从表中 eid 为 3 对应的记录的 deptid 被设置为 NULL。

尝试对主表数据进行删除，然后再查询主表与从表的数据：

1
2
3
4
DELETE FROM dept WHERE did = 1001;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中 did 为 1001 的记录被删除，在 emp 表中，引用了 dept 表中 did 为 1001 的记录也都被删除，即 eid 为 1 和 2 的两条记录被删除了。

演示三：ON UPDATE CASCADE ON DELETE CASCADE

先建表：

1
2
3
4
5
6
7
8
9
10
11
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
);

分别插入数据：

1
2
3
4
5
6
7
INSERT INTO dept VALUES(1001, '教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');

INSERT INTO emp VALUES(1, '张三', 1001);
INSERT INTO emp VALUES(2, '李四', 1001);
INSERT INTO emp VALUES(3, '王五', 1002);

对主表数据进行修改，然后再查询主表与从表的数据：

1
2
3
4
UPDATE dept SET did = 1004 WHERE did = 1002;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中，原 did 为 1002 的记录被修改为 1004，在从表中 eid 为 3 对应的记录的 deptid 也被修改为 1004。

尝试对主表数据进行删除，然后再查询主表与从表的数据：

1
2
3
4
DELETE FROM dept WHERE did = 1001;

SELECT * FROM dept;
SELECT * FROM emp;

会发现 dept 表中 did 为 1001 的记录被删除，在 emp 表中，引用了 dept 表中 did 为 1001 的记录也都被删除，即 eid 为 1 和 2 的两条记录被删除了。

6.5 删除外键约束

流程如下：

1
2
3
4
5
6
7
8
9
# 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; # 查看某个表的约束名

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

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

ALTER TABLE 从表名 DROP INDEX 索引名;

使用 emp 和 dept 表进行举例，建表语句如下：

1
2
3
4
5
6
7
8
9
10
11
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 表的约束名：

1
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';

emp表的约束信息

删除 emp 表的外键：

1
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;

删除外键约束后，必须手动删除对应的索引。因此先查看 emp 表中的索引信息：

1
SHOW INDEX FROM emp;

emp表的索引信息

删除索引名称为 deptid 的索引信息：

1
ALTER TABLE emp DROP INDEX deptid;

6.6 外键的疑问

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

不是的。

外键约束是否建立有什么区别？

建立外键约束后，创建表、删除表、添加数据、修改数据、删除数据等操作会从语法层面受到限制。例如在员工表中不可能添加一条部门列对应的部门信息在部门表中不存在的员工信息。

不建立外键约束时，创建表、删除表、添加数据、修改数据、删除数据等操作将不受限制，如果要保证数据的引用完整性，只能依靠 SQL 外的程序限定。

外键是否建立和查询有没有关系？

没有关系。

外键的性能影响

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，使用外键约束可能并不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销使得 SQL 操作效率降低。所以， MySQL 允许不使用系统自带的外键约束，而是在应用层面完成检查数据一致性的逻辑。也就是说，即使不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

6.7 阿里开发规范

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

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

7. 检查约束

7.1 检查约束概述

使用 CHECK 关键字可以检查某个字段的值是否符合某些要求，这些要求一般指值的范围。

需要注意的是，在 MySQL 中并不支持 CHECK 约束，尽管可以使用 CHECK 关键字，但其对数据的验证没有任何效果，添加非法数据时，也不会有任何错误或警告。

7.2 CHECK 约束的使用

创建表时添加 CHECK 约束

比如：

1
2
3
4
5
CREATE TABLE employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(5),
    gender CHAR CHECK ('男' OR '女')
);

添加数据：

1
INSERT INTO employee VALUES(1, '张三', '妖');

尽管 gender 的值并不符合制定的检查约束，但这条数据仍然可以插入成功。

创建表后添加 CHECK 约束

先新建表：

1
2
3
4
5
6
CREATE TABLE temp(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT CHECK(age > 20),
    PRIMARY KEY(id)
);

对已有列添加 CHECK 约束：

1
ALTER TABLE temp ADD CONSTRAINT age CHECK(age > 20);

添加新列并添加 CHECK 约束：

1
2
3
ALTER TABLE temp ADD sex CHAR(2) CHECK(sex IN(‘男’, ’女’));

ALTER TABLE temp ADD height INT CHECK(height >= 0 AND height < 3);

8. 默认值约束

8.1 默认值约束的概述

使用 DEFAULT 关键词可以给某个字段指定默认值，之后在插入数据时，如果指定了默认值的字段没有显式赋值，那么会被赋值为默认值。

一般不会在拥有唯一约束或主键约束的列上添加默认值约束。

8.2 添加默认值约束

创建表时添加默认值约束

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE 表名称(
    字段名  数据类型  PRIMARY KEY,
    字段名  数据类型  UNIQUE KEY NOT NULL
    字段名  数据类型  UNIQUE KEY,
    字段名  数据类型  NOT NULL DEFAULT 默认值, 
);
CREATE TABLE 表名称(
    字段名  数据类型 DEFAULT 默认值 ,
    字段名  数据类型 NOT NULL DEFAULT 默认值,  
    字段名  数据类型 NOT NULL DEFAULT 默认值,
    PRIMARY(字段名),
    UNIQUE KEY(字段名)
);

比如：

1
2
3
4
5
6
CREATE TABLE employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    gender CHAR DEFAULT '男',
    tel CHAR(11) NOT NULL DEFAULT '' # 默认是空字符串
);

向表中添加数据并查看表中数据：

1
2
INSERT INTO employee VALUES(1, '默烦','男','15233334444');
INSERT INTO employee(eid,ename) VALUES(2, 'mofan');

使用默认值约束的记录

创建表后添加默认值约束

语法：

1
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值;

如果添加默认值约束的列原本存在非空约束，想要继续保留非空约束，那么在修改表时，还需要保留非空约束，否则非空约束会被删除。同理，给一个拥有默认值约束的列添加非空约束也是这样。比如：

1
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL;

创建一个表：

1
2
3
4
5
6
CREATE TABLE employee(
    eid INT PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    gender CHAR,
    tel CHAR(11) NOT NULL
);

添加默认值约束：

1
2
3
4
5
ALTER TABLE employee MODIFY gender CHAR DEFAULT '男'; 
ALTER TABLE employee MODIFY tel CHAR(11) DEFAULT '';

# 给 tel 增加默认值约束，并保留非空约束
ALTER TABLE employee MODIFY tel CHAR(11) DEFAULT '' NOT NULL;

8.3 删除默认值约束

语法：

1
2
3
4
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

# 删除默认值约束，保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

比如：

1
2
3
4
ALTER TABLE employee MODIFY gender CHAR; 

# 删除默认值约束，但保留非空约束
ALTER TABLE employee MODIFY tel CHAR(11) NOT NULL;

9. 简单的面试题

为什么建表时，加 NOT NULL DEFAULT '' 或 DEFAULT 0？

不想要表中出现 NULL 值。

为什么不想要表中出现 NULL 值？

1、不好进行比较。NULL 是一种特殊值，只能用专门的 IS NULL 和 IS NOT NULL 进行比较。碰到运算符时通常返回 NULL。

2、效率不高。NULL 值会影响索引效果。因此，常常在建表时加 NOT NULL DEFAULT '' 或 DEFAULT 0。

带 AUTO_INCREMENT 约束的字段值是从 1 开始的吗？

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

每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。

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