MySQL约束

Posted on 2022-04-13 Edited on 2024-07-22 In 数据库 , MySQL Views: Word count in article: 25k Reading time ≈ 23 mins.

1、概述

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
- 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录。
- 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”。
- 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门。
- 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
约束是表级的强制规定。可以在创建表时规定约束（通过CREATE TABLE语句），或者在表创建之后通过ALTER TABLE语句规定约束。

约束的分类
根据约束数据列的限制，约束可分为：
- 单列约束：每个约束只约束一列。
- 多列约束：每个约束可约束多列数据。
根据约束的作用范围，约束可分为：
- 列级约束：只能作用在一个列上，跟在列的定义后面。
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义。
根据约束起的作用，约束可分为：
- NOT NULL非空约束，规定某个字段不能为空。
- UNIQUE唯一约束，规定某个字段在整个表中是唯一的。
- PRIMARY KEY主键(非空且唯一)约束。
- FOREIGN KEY外键约束。
- CHECK检查约束。
- DEFAULT默认值约束。
注意：MySQL不支持check约束，但可以使用check约束，而没有任何效果。

查看某个表已有的约束。

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

添加约束。

1
2
3

CREATE TABLE 时添加约束

ALTER TABLE 时增加约束、删除约束

2、非空约束

作用

限定某个字段/某列的值不允许为空。

关键字

NOT NULL

特点

默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型。
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空。
一个表可以有很多列都分别限定了非空。
空字符串’’不等于NULL，0也不等于NULL。

添加非空约束

建表时：

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

例子：

CREATE TABLE test1(
    id INT NOT NULL,
    last_name VARCHAR(15) NOT NULL,
    email VARCHAR(25),
    salary DECIMAL(10,2)
);

INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

mysql> INSERT INTO test1(id,last_name,email,salary) VALUES(2,NULL,'tom1@126.com',3400);
ERROR 1048 (23000): Column 'last_name' cannot be null

mysql> INSERT INTO test1(id,last_name,email,salary) VALUES(NULL,'Jerry','jerry@126.com',3400);
ERROR 1048 (23000): Column 'id' cannot be null

mysql> INSERT INTO test1(id,email) VALUES(2,'abc@126.com');
ERROR 1364 (HY000): Field 'last_name' doesn’t have a default value

mysql> UPDATE test1 SET last_name = NULL WHERE id = 1;
ERROR 1048 (23000): Column 'last_name' cannot be null

UPDATE test1
SET email = 'tom@126.com'
WHERE id = 1;

建表后：

1	alter table 表名称 modify 字段名数据类型 not null;

例子：

1 2	ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL;

删除非空约束

格式如下：

# 去掉not null，相当于修改某个非注解字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型 NULL; 
# 或
alter table 表名称 modify 字段名 数据类型;

3、唯一性约束

作用

用来限制某个字段/某列的值不能重复。

关键字

UNIQUE

特点

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

添加唯一约束

建表时：

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
# 或
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

例子：

CREATE TABLE test2(
    id INT UNIQUE, # 列级约束
    last_name VARCHAR(15) ,
    email VARCHAR(25),
    salary DECIMAL(10,2),
    # 表级约束
    CONSTRAINT uk_test2_email UNIQUE(email)
);

mysql> 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    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.table_constraints  WHERE table_name = 'test2';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | dbtest13          | id              | dbtest13     | test2      | UNIQUE          | YES      |
| def                | dbtest13          | uk_test2_email  | dbtest13     | test2      | UNIQUE          | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.01 sec)

INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',4500);

mysql> INSERT INTO test2(id,last_name,email,salary) VALUES(1,'Tom1','tom1@126.com',4600);
ERROR 1062 (23000): Duplicate entry '1' for key 'test2.id'

mysql> INSERT INTO test2(id,last_name,email,salary) VALUES(2,'Tom1','tom@126.com',4600);
ERROR 1062 (23000): Duplicate entry 'tom@126.com' for key 'test2.uk_test2_email'

# 可以向声明为unique的字段上添加null值。而且可以多次添加null
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);
INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);

mysql> SELECT * FROM test2;
+------+-----------+-------------+---------+
| id   | last_name | email       | salary  |
+------+-----------+-------------+---------+
|    1 | Tom       | tom@126.com | 4500.00 |
|    2 | Tom1      | NULL        | 4600.00 |
|    3 | Tom2      | NULL        | 4600.00 |
+------+-----------+-------------+---------+
3 rows in set (0.00 sec)

建表后：

1	alter table 表名称 modify 字段名数据类型 not null;

例子：

UPDATE test2 SET salary = 5000 WHERE id = 3;

# 方式1：
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

# 方式2：
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

关于复合唯一约束

格式如下：

create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    unique key(字段列表) # 字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

例子：

CREATE TABLE USER(
    id INT,
    `name` VARCHAR(15),
    `password` VARCHAR(25),
    # 表级约束
    CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);

INSERT INTO USER
VALUES(1,'Tom','abc');

INSERT INTO USER
VALUES(1,'Tom1','abc');

mysql> INSERT INTO USER VALUES(1,'Tom1','abc');
ERROR 1062 (23000): Duplicate entry 'Tom1-abc' for key 'USER.uk_user_name_pwd'

mysql> SELECT * FROM USER;
+------+------+----------+
| id   | name | password |
+------+------+----------+
|    1 | Tom  | abc      |
|    1 | Tom1 | abc      |
+------+------+----------+
2 rows in set (0.00 sec)

# 学生表
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)  # 复合唯一
);

INSERT INTO student VALUES(1,'张三','13710011002','101223199012015623');# 成功
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');# 成功
INSERT INTO course VALUES(1001,'Java'),(1002,'MySQL');# 成功

mysql> SELECT * FROM student;
+------+--------+-------------+--------------------+
| sid  | sname  | tel         | cardid             |
+------+--------+-------------+--------------------+
|    1 | 张三   | 13710011002 | 101223199012015623 |
|    2 | 李四   | 13710011003 | 101223199012015624 |
+------+--------+-------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)

INSERT INTO student_course VALUES
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);# 成功

mysql> SELECT * FROM student_course;
+------+------+------+-------+
| id   | sid  | cid  | score |
+------+------+------+-------+
|    1 |    1 | 1001 |    89 |
|    2 |    1 | 1002 |    90 |
|    3 |    2 | 1001 |    88 |
|    4 |    2 | 1002 |    56 |
+------+------+------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO student_course VALUES (5,2,1002,67);
ERROR 1062 (23000): Duplicate entry '2-1002' for key 'student_course.sid'

删除唯一性约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'student_course';

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'test2';

# 删除唯一性索引
ALTER TABLE test2
DROP INDEX last_name;

ALTER TABLE test2
DROP INDEX uk_test2_sal;

注意：可以通过 show index from 表名称; 查看表的索引。

4、PRIMARY KEY约束

作用

用来唯一标识表中的一行记录。

关键字

primary key

特点

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

添加主键约束

建表时：

create table 表名称(
字段名 数据类型 primary key, # 列级模式
字段名 数据类型,
字段名 数据类型
);
# 或
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) # 表级模式
);

例子：

# 错误：Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, # 列级约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

# 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录。
CREATE TABLE test4(
    id INT PRIMARY KEY, # 列级约束
    last_name VARCHAR(15),
    salary DECIMAL(10,2),
    email VARCHAR(25)
);

# MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
CREATE TABLE test5(
    id INT , 
    last_name VARCHAR(15),
    salary DECIMAL(10,2),
    email VARCHAR(25),
    # 表级约束
    CONSTRAINT pk_test5_id PRIMARY KEY(id)  # 没有必要起名字。
);

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'test4';

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'test5';

INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');

mysql> INSERT INTO test4(id,last_name,salary,email) VALUES(1,'Tom',4500,'tom@126.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'test4.PRIMARY'

mysql> INSERT INTO test4(id,last_name,salary,email) VALUES(NULL,'Tom',4500,'tom@126.com');
ERROR 1048 (23000): Column 'id' cannot be null

mysql> SELECT * FROM test4;
+----+-----------+---------+-------------+
| id | last_name | salary  | email       |
+----+-----------+---------+-------------+
|  1 | Tom       | 4500.00 | tom@126.com |
+----+-----------+---------+-------------+
1 row in set (0.00 sec)

建表后：

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

例子：

CREATE TABLE test6(
    id INT,
    last_name VARCHAR(15),
    salary DECIMAL(10,2),
    email VARCHAR(25)
);

ALTER TABLE test6
ADD PRIMARY KEY (id);

mysql> DESC test6;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   | PRI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

关于复合主键

格式如下：

create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    primary key(字段名1,字段名2) # 表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

例子：

CREATE TABLE user1(
    id INT,
    NAME VARCHAR(15),
    PASSWORD VARCHAR(25),
    PRIMARY KEY (NAME,PASSWORD)
);

# 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
INSERT INTO user1
VALUES(1,'Tom','abc');

INSERT INTO user1
VALUES(1,'Tom1','abc');

mysql> INSERT INTO user1 VALUES(1,NULL,'abc');
ERROR 1048 (23000): Column 'NAME' cannot be null

mysql> SELECT * FROM user1;
+------+------+----------+
| id   | NAME | PASSWORD |
+------+------+----------+
|    1 | Tom  | abc      |
|    1 | Tom1 | abc      |
+------+------+----------+
2 rows in set (0.00 sec)

删除主键约束

格式如下：

1	alter table 表名称 drop primary key;

例子：

# 在实际开发中，不会去删除表中的主键约束！
ALTER TABLE test6
DROP PRIMARY KEY;

mysql> DESC test6;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   |     | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5、自增列：AUTO_INCREMENT

作用

某个字段的值自增。

关键字

auto_increment

特点和要求

一个表最多只能有一个自增长列。
当需要产生唯一标识符或顺序值时，可设置自增长。
自增长列约束的列必须是键列（主键列，唯一键列）。
自增约束的列的数据类型必须是整数类型。

如果自增列指定了0和null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

# 错误演示
# ERROR 1075 (42000): 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)
);

# ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类型
create table employee(
    eid int primary key,
    ename varchar(20) unique key auto_increment
);

如何指定自增约束

建表时：

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(字段名)
);

例子：

CREATE TABLE test7(
    id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(15) 
);

# 开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键
# 对应的字段去赋值了。
# 执行三次
INSERT INTO test7(last_name)
VALUES('Tom');

mysql> SELECT * FROM test7;
+----+-----------+
| id | last_name |
+----+-----------+
|  1 | Tom       |
|  2 | Tom       |
|  3 | Tom       |
+----+-----------+
3 rows in set (0.00 sec)

# 当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值
INSERT INTO test7(id,last_name)
VALUES(0,'Tom');
INSERT INTO test7(id,last_name)
VALUES(NULL,'Tom');

INSERT INTO test7(id,last_name)
VALUES(10,'Tom');

INSERT INTO test7(id,last_name)
VALUES(-10,'Tom');

mysql> SELECT * FROM test7;
+-----+-----------+
| id  | last_name |
+-----+-----------+
| -10 | Tom       |
|   1 | Tom       |
|   2 | Tom       |
|   3 | Tom       |
|   4 | Tom       |
|   5 | Tom       |
|  10 | Tom       |
+-----+-----------+
7 rows in set (0.01 sec)

建表后：

1	alter table 表名称 modify 字段名数据类型 auto_increment;

例子：

CREATE TABLE test8(
    id INT PRIMARY KEY ,
    last_name VARCHAR(15) 
);

mysql> DESC test8;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| last_name | varchar(15) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;

mysql> DESC test8;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| last_name | varchar(15) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

如何删除自增约束

格式如下：

1 2	# alter table 表名称 modify 字段名数据类型 auto_increment;# 给这个字段增加自增约束 alter table 表名称 modify 字段名数据类型; # 去掉auto_increment相当于删除

例子：

ALTER TABLE test8 MODIFY id INT;

mysql> DESC test8;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| last_name | varchar(15) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。在MySQL 5.7版本中，测试步骤如下：

# 在MySQL 5.7中演示
CREATE TABLE test9(
	id INT PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test9
VALUES(0),(0),(0),(0);

mysql> SELECT * FROM test9;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

DELETE FROM test9
WHERE id = 4;

INSERT INTO test9
VALUES(0);

mysql> SELECT * FROM test9;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
4 rows in set (0.00 sec)

DELETE FROM test9
WHERE id = 5;

mysql> SELECT * FROM test9;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

# 重启MySQL，重新插入一个空值。
INSERT INTO test9
VALUES(0);

mysql> SELECT * FROM test9;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

从结果可以看出，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。在MySQL 8.0版本中，上述测试步骤最后一步的结果如下：

mysql> SELECT * FROM test9;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)

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

6、FOREIGN KEY约束

作用

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

关键字

FOREIGN KEY

主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表。
从表（子表）：引用别人的表，参考别人的表。
- 例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
- 例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列，因为被依赖/被参考的值必须是唯一的。
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表。
删表时，先删从表（或先删除外键约束），再删除主表。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
在“从表”中指定外键约束，并且一个表可以建立多个外键约束。
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。例如：都是表示部门编号，都是int类型。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
删除外键约束后，必须手动删除对应的索引。

添加外键约束

建表时：

create table 主表名称(
    字段1 数据类型 primary key,
    字段2 数据类型
);
create table 从表名称(
    字段1 数据类型 primary key,
    字段2 数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
# (从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
# (从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

例子：

# ①先创建主表
CREATE TABLE dept1(
    dept_id INT,
    dept_name VARCHAR(15)
);

# ②再创建从表
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)
);

# 上述操作报错，因为主表中的dept_id上没有主键约束或唯一性约束。
# ③ 添加
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);

mysql> DESC dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int         | NO   | PRI | NULL    |       |
| dept_name | varchar(15) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# ④ 再创建从表
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)
);

mysql> 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    |                |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp1';

# 添加失败
mysql> INSERT INTO emp1 VALUES(1001,'Tom',10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbtest13`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))

INSERT INTO dept1
VALUES(10,'IT');

# 在主表dept1中添加了10号部门以后，我们就可以在从表中添加10号部门的员工
INSERT INTO emp1
VALUES(1001,'Tom',10);

# 删除失败
mysql> DELETE FROM dept1 WHERE dept_id = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbtest13`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))

# 更新失败
mysql> UPDATE dept1 SET dept_id = 20 WHERE dept_id = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbtest13`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))

建表后：一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

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

例子：

CREATE TABLE dept2(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(15)
);

CREATE TABLE emp2(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(15),
    department_id INT
);

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp2';

约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null。
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作。
Restrict方式：同no action，都是立即检查外键约束。如果没有指定等级，就相当于Restrict方式。

Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别。

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

# 测试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);

UPDATE dept
SET did = 1004
WHERE did = 1002;

mysql> SELECT * FROM dept;
+------+-----------+
| did  | dname     |
+------+-----------+
| 1001 | 教学部    |
| 1003 | 咨询部    |
| 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)

DELETE FROM dept
WHERE did = 1004;

mysql> SELECT * FROM dept;
+------+-----------+
| did  | dname     |
+------+-----------+
| 1001 | 教学部    |
| 1003 | 咨询部    |
+------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM emp;
+-----+--------+--------+
| eid | ename  | deptid |
+-----+--------+--------+
|   1 | 张三   |   1001 |
|   2 | 李四   |   1001 |
|   3 | 王五   |   NULL |
+-----+--------+--------+
3 rows in set (0.00 sec)

删除外键约束

流程如下：

# 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; # 查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

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

例子：

1	mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp1';

1
2
3

# 删除外键约束
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

1 2	# 再手动的删除外键约束对应的普通索引 mysql> SHOW INDEX FROM emp1;

1 2	ALTER TABLE emp1 DROP INDEX fk_emp1_dept_id;

开发场景

如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？
- 不是的。
建和不建外键约束有什么区别？
- 建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。
- 不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。
那么建和不建外键约束和查询有没有关系？
- 没有。
- 在MySQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
- 阿里开发规范
  - 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

7、CHECK约束

作用

检查某个字段的值是否符号xx要求，一般指的是值的范围。
MySQL5.7可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告。 但是MySQL 8.0中可以使用check约束了。

关键词

CHECK。

创建check约束

建表时：

CREATE TABLE test10(
    id INT,
    last_name VARCHAR(15),
    salary DECIMAL(10,2) CHECK(salary > 2000)
);

INSERT INTO test10
VALUES(1,'Tom',2500);

# 添加失败
mysql> INSERT INTO test10 VALUES(2,'Tom1',1500);
ERROR 3819 (HY000): Check constraint 'test10_chk_1' is violated.

mysql> SELECT * FROM test10;
+------+-----------+---------+
| id   | last_name | salary  |
+------+-----------+---------+
|    1 | Tom       | 2500.00 |
+------+-----------+---------+
1 row in set (0.00 sec)

8、DEFAULT约束

作用

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

关键字

DEFAULT

如何给字段加默认值

建表时：

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 test11(
    id INT,
    last_name VARCHAR(15),
    salary DECIMAL(10,2) DEFAULT 2000
);

INSERT INTO test11(id,last_name,salary)
VALUES(1,'Tom',3000);

INSERT INTO test11(id,last_name)
VALUES(2,'Tom1');

mysql> SELECT *  FROM test11;
+------+-----------+---------+
| id   | last_name | salary  |
+------+-----------+---------+
|    1 | Tom       | 3000.00 |
|    2 | Tom1      | 2000.00 |
+------+-----------+---------+
2 rows in set (0.00 sec)

mysql> DESC test11;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  |     | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | 2000.00 |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

建表时：

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

例子：

CREATE TABLE test12(
    id INT,
    last_name VARCHAR(15),
    salary DECIMAL(10,2)
);

ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;

mysql> DESC test12;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int          | YES  |     | NULL    |       |
| last_name | varchar(15)  | YES  |     | NULL    |       |
| salary    | decimal(8,2) | YES  |     | 2500.00 |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

如何删除默认值约束

格式如下：

1
2

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

例子：

1 2	ALTER TABLE test12 MODIFY salary DECIMAL(8,2);

9、总结

为什么建表时，加not null default’’或default 0。
- 不想让表中出现null值。
为什么不想要null的值。
- 不好比较。null是一种特殊值，比较时只能用专门的is null和is not null来比较。碰到运算符，通常返回null。
- 效率不高。影响提高索引效果。因此，我们往往在建表时not null default’’或default 0。
带AUTO_INCREMENT约束的字段值是从1开始的吗？
- 在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
并不是每个表都可以任意选择存储引擎？
- 外键约束（FOREIGN KEY）不能跨引擎使用。
- MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。