MySQL约束解析：分类、查看与增删操作详解

---

在数据库管理中，保障数据的完整性和准确性是至关重要的任务。MySQL 作为一款广泛使用的关系型数据库管理系统，提供了多种约束机制来确保数据符合特定的规则和条件。这些约束不仅能够防止无效或错误的数据进入数据库，还能提高数据的一致性和可靠性。本文将详细介绍 MySQL 中各类约束的相关知识，包括约束的分类、查看方法以及常见约束的添加与删除操作。

---

1. 约束了解

SQL 规范中的 约束 是保障数据完整性的关键手段。数据完整性即数据的精确性与可靠性，旨在防止数据库出现语义不符的数据，避免因错误信息的输入输出导致无效操作或错误信息。约束通过对表数据附加条件限制，实现保障数据完整性的目标。

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

1.1 约束分类

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

• 单列约束：每个约束只约束一列
• 多列约束：每个约束可约束多列数据

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

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，而是单独定义

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

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

• NOT NULL：非空约束，规定某个字段不能为空
• UNIQUE：唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY：主键(非空且唯一)约束
• FOREIGN KEY：外键约束
• CHECK：检查约束
• DEFAULT：默认值约束

MySQL8.0.16之前的版本并不支持 CHECK 约束，但可以使用，只是没有任何效果罢了；从 MySQL 8.0.16 版本开始，MySQL 开始支持 CHECK 约束，并且会强制执行。

1.2 查看约束

在 MySQL 中，可以通过以下几种方式查看某个表已有的约束：

• 使用 SHOW CREATE TABLE 语句

该语句会返回创建指定表的 SQL 语句，其中会包含表定义时设置的所有约束信息。

SHOW CREATE TABLE table_name;

mysql> SHOW CREATE TABLE `order`;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| order | CREATE TABLE `order` (`order_id` int DEFAULT NULL, `order_name` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

执行此语句后，结果集包含两列：Table（表名）和 Create Table（创建表的 SQL 语句）。在 Create Table 列中，你能看到定义的主键、唯一键、外键等约束信息。

• 查询 information_schema.table_constraints 表

information_schema 是 MySQL 自带的系统数据库，其中的 table_constraints 表存储了数据库中所有表的约束信息。

SELECT 
    CONSTRAINT_NAME, 
    CONSTRAINT_TYPE
FROM 
    information_schema.table_constraints
WHERE 
    TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'your_table_name';

• 举例

SELECT
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM
    information_schema.table_constraints
WHERE
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'employees';

此查询会返回指定表中所有约束的名称和类型，如 PRIMARY KEY、UNIQUE、FOREIGN KEY 等。

• 若只是想快速查看单个表的完整创建语句，使用 SHOW CREATE TABLE 更合适。
• 如果需要批量获取多个表的约束信息，或者需要对约束信息进行筛选和处理，查询 information_schema.table_constraints 表是更好的选择。

---

2. 非空约束（NOT NULL）

非空约束规定了表中的某一列不允许插入 NULL 值。 NULL 在数据库里代表没有值，如果给设置了非空约束的列插入 NULL，数据库会拒绝该操作。

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

2.1 添加非空约束

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

# 建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

• 举例：

# 创建表的时间添加非空约束
CREATE TABLE student(
	sid int,
	sname varchar(20) not null,
	tel char(11) ,
	cardid char(18) not null
);

# 插入数据进行测试
insert into student values(1,'张三','13710011002','110222198912032545'); #成功

insert into student values(2,'李四','13710011002',null);#身份证号为空，失败
ERROR 1048 (23000): Column 'cardid' cannot be null

insert into student values(2,'李四',null,'110222198912032546');#成功，tel允许为空

insert into student values(3,null,null,'110222198912032547');#失败
ERROR 1048 (23000): Column 'sname' cannot be null

# 建表后添加非空约束
alter table student modify tel char(11) not null;

使用 SHOW CREATE TABLE 语句查验

2.2 删除非空约束

运用 ALTER TABLE 语句来修改表结构，借助 MODIFY 子句重新定义列，把 NOT NULL 约束去掉。

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

• 举例

alter table student modify sname varchar(20) null;

使用 SHOW CREATE TABLE 语句查验

3. 唯一性约束（UNIQUE）

唯一性约束规定表中的指定列或列组合中的数据必须是唯一的，不允许出现重复值。与主键约束类似，都能保证数据的唯一性，但一个表可以有多个唯一性约束，而主键约束只能有一个。

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

3.1 添加唯一约束

创建表时，可以在列定义后直接添加 UNIQUE，也可以在表定义的最后使用 CONSTRAINT 关键字来指定唯一性约束。

• 字段列表中如果是一个字段，表示该列的值唯一。
• 如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的。

• 语法一：在列定义后直接添加

create table 表名称(
	字段名 数据类型,
	字段名 数据类型 unique,
	字段名 数据类型 unique key,
	字段名 数据类型
);

### 举例：
create table student(
	sid int,
	sname varchar(20),
	tel char(11) unique,
	cardid char(18) unique key
);

• 语法二：使用 CONSTRAINT 关键字来指定

# 语法二：使用 CONSTRAINT 关键字来指定
create table 表名称(
	字段名 数据类型,
	字段名 数据类型,
	字段名 数据类型,
	[constraint 约束名] unique key(字段名)
);

### 举例：
create table student(
	sid int,
	sname varchar(20),
	tel char(11),
	cardid char(18),
	constraint uk_name_pwd unique(tel,cardid)
);
# 表示字段组合不能重复

• 建表后指定唯一键约束

#方式1：
alter table 表名称 add unique key(字段列表);

#方式2：
alter table 表名称 modify 字段名 字段类型 unique;

### 举例：
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);

使用 SHOW CREATE TABLE 语句查验

3.2 删除唯一约束(要改)

在 MySQL 中，当为列添加唯一性约束时，会自动创建唯一索引。删除唯一约束时可以直接使用ALTER TABLE语句来删除唯一约束，也可以通过删除唯一索引的方式。

• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和 () 中排在第一个的列名相同。也可以自定义唯一性约束名。这段话对不对、

#查看都有哪些约束
SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = '表名'; 

# 例如
SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = 'student'; 

SHOW INDEX 语句可以显示表中的所有索引信息，包括唯一索引。

SHOW INDEX FROM student;

# 通过约束名删除，适用于MySQL 8.0及以上版本
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

# 删除唯一约束索引方式删除
ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE student DROP CONSTRAINT tel;
ALTER TABLE student DROP INDEX tel;

4. 主键约束（PRIMARY KEY）

主键约束（Primary Key Constraint）用于唯一标识表中的每一行记录。确保表中任意两行数据在主键列上的值都不相同，并不允许包含空值（NULL）。

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

需要注意的是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

4.1 添加主键约束

• 建表时指定主键约束

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

演示类似唯一约束的属性

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

# 查看表结构
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field |        Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|    id |     int(11) |   NO | PRI |    NULL |       |
|  name | varchar(20) |  YES |     |    NULL |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 插入数据
insert into temp values(1,'张三');#成功
insert into temp values(2,'李四');#成功

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 |  张三 |
|  2 |  李四 |
+----+------+
2 rows in set (0.00 sec)

insert into temp values(1,'张三');#失败
ERROR 1062 (23000): Duplicate（重复） entry（键入，输入） '1' for key 'PRIMARY'

insert into temp values(1,'王五');#失败
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into temp values(3,'张三');#成功

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 |  张三 |
|  2 |  李四 |
|  3 |  张三 |
+----+------+
3 rows in set (0.00 sec)

演示类似非空约束的属性

insert into temp values(4,null);#成功
insert into temp values(null,'李琦');#失败
ERROR 1048 (23000): Column 'id' cannot be null

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 |  张三 |
|  2 |  李四 |
|  3 |  张三 |
|  4 | NULL |
+----+------+
4 rows in set (0.00 sec)

#演示一个表建立两个主键约束
create table temp(
	id int primary key,
	name varchar(20) primary key
);
ERROR 1068 (42000): Multiple（多重的） primary key defined（定义）

• 建表后增加主键约束

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

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

4.2 删除主键约束

在 MySQL 里，使用 ALTER TABLE 语句结合 DROP PRIMARY KEY 子句来删除主键约束。

alter table 表名称 drop primary key;

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

删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

5. 自增列（AUTO_INCREMENT）

自增列约束规定表中的某一列的值会在每次插入新记录时自动递增。该列通常是整数类型，并且初始值一般为 1，后续每插入一条新记录，该列的值就会在前一个值的基础上自动加 1。

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

5.1 指定自增约束

• 建表时指定

create table 表名称(
	字段名 数据类型 primary key auto_increment,
	字段名 数据类型 unique key not null,
	字段名 数据类型 unique key,
	字段名 数据类型 not null default 默认值,
);

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

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

• 建表后指定

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

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

alter table employee modify eid int auto_increment;

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

5.2 如何删除自增约束

在 MySQL 里，自增约束通常与 AUTO_INCREMENT属性相关联。先使用SHOW CREATE TABLE语句查看表结构，确认自增列的名称和当前的表定义。然后使用ALTER TABLE语句修改表结构，去除AUTO_INCREMENT` 属性。

#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型 auto_increment;  

# #去掉auto_increment相当于删除
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

alter table employee modify eid int;

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

5.3 自增变量的持久化（8.0新增）

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

下面通过案例来对比不同的版本中自增变量是否持久化。

• 在MySQL 5.7版本中，测试步骤如下：

# 创建的数据表中包含自增主键的id字段，语句如下：
CREATE TABLE test1(
	id INT PRIMARY KEY AUTO_INCREMENT
);

# 插入4个空值，执行如下：
INSERT INTO test1
VALUES(0),(0),(0),(0);

# 查询数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

# 删除id为4的记录，语句如下：
DELETE FROM test1 WHERE id = 4;

# 再次插入一个空值，语句如下：
INSERT INTO test1 VALUES(0);

# 查询此时数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
4 rows in set (0.00 sec)

# 从结果可以看出，虽然删除了id为4的记录，但是再次插入空值时，并没有重用被删除的4，而是分配了5。 删除id为5的记录，结果如下：
DELETE FROM test1 where id=5;

# 重启数据库，重新插入一个空值。
INSERT INTO test1 values(0);

# 再次查询数据表test1中的数据，结果如下：
mysql> SELECT * FROM test1;
+----+
| 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 test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)
# 从结果可以看出，自增变量已经持久化了。

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

6. 外键约束（FOREIGN KEY）

外键约束（Foreign Key Constraint）用于建立两个表之间的关联关系。

外键是一个表中的字段，它与另一个表的主键或唯一键相对应。包含外键的表称为子表（或从表），外键所引用的表称为父表（或主表）。

外键约束确保子表中存储的外键值在父表的对应列中存在，从而维护了表间数据的一致性和完整性。它可以防止在子表中插入无效的关联数据，也能在一定程度上控制数据的删除和更新操作，避免出现孤立数据或数据不一致的情况。

• 主表和从表/父表和子表

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

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表；学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

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

6.1 添加外键约束

• 建表时添加外键约束

create table 主表名称(
	字段1 数据类型 primary key,
	字段2 数据类型
);
create table 从表名称(
	字段1 数据类型 primary key,
	字段2 数据类型,
	[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);

# (从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
# (从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

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

• 建表后添加外键约束

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

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

# 举例
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

create table dept(
	did int primary key, #部门编号
	dname varchar(50) #部门名称
);
create table emp(
	eid int primary key, #员工编号
	ename varchar(5), #员工姓名
	deptid int #员工所在的部门
);

#这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table emp add foreign key (deptid) references dept(did);

总之约束关系是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束
• 添加了外键约束后，从表的添加和修改数据受约束
• 在从表上建立外键，要求主表必须存在
• 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

6.2 外键约束处理方式

外键约束处理方式是在数据库管理中，用于维护表之间关联关系数据完整性的机制。是在数据库中用于定义当父表中的数据发生变化（更新或删除）时，子表中相关数据应如何处理的规则。

常见的外键约束处理方式有以下几种：

1. Cascade方式：当父表中的记录被更新或删除时，子表中与之匹配的记录也会被同步更新或删除。这种方式可以确保数据的一致性，避免出现孤立记录。
2. Set null方式：当父表中的记录被更新或删除时，子表中匹配记录的外键列会被设置为null。使用这种方式的前提是子表的外键列允许为null。
3. No action方式：如果子表中有匹配的记录，那么不允许对父表中对应的候选键进行更新或删除操作。该操作会检查外键约束，但与Restrict方式不同的是，No action是在语句执行完毕后检查，而不是立即检查。
4. Restrict方式：与No action类似，都是不允许在子表有匹配记录时对父表进行更新或删除操作。不过，Restrict是立即检查外键约束，即在操作执行时就进行检查，若违反约束则操作直接失败。
5. Set default方式：当父表中的记录发生变更时，子表中的外键列会被设置为一个默认值。然而，Innodb存储引擎不能识别这种方式，在可视化工具SQLyog中可能显示空白。

在实际应用中，选择合适的外键约束处理方式至关重要，它直接影响到数据的完整性和一致性。例如，对于具有强关联关系的数据，如订单与订单详情，适合使用Cascade方式；而对于一些允许存在临时孤立数据的情况，Set null方式可能更合适。

• 未指定等级默认采用 Restrict 方式

---

• 在数据库中，当创建外键约束时如果没有明确指定在父表进行 UPDATE 或 DELETE 操作时的处理方式，很多数据库（如 MySQL）默认行为类似于 Restrict 方式。
• Restrict 方式意味着在进行 UPDATE 或 DELETE 操作时会立即检查外键约束，如果子表中存在与父表关联的记录，那么对父表对应记录的 UPDATE 或 DELETE 操作会被阻止。这种默认行为有助于保证数据的引用完整性，避免误操作破坏表之间的关联关系。

设置外键约束处理方式

在创建表时设置

-- 创建父表
CREATE TABLE parent_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 创建子表，并设置外键约束处理方式
CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
        ON UPDATE CASCADE        # 设置外键约束处理方式更新CASCADE  
        ON DELETE RESTRICT       # 设置外键约束处理方式删除RESTRICT
);    

在修改表结构时设置

-- 修改表结构，设置外键约束处理方式
ALTER TABLE child_table
DROP FOREIGN KEY fk_name; -- 先删除原有的外键约束，fk_name 是外键约束名

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

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

6.3 删除外键约束

在 MySQL 里，可使用 ALTER TABLE 语句结合 DROP FOREIGN KEY 子句来删除外键约束。

• 第一步先查看约束名和删除外键约束

#查看某个表的约束名
SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = '表名称';

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

• 第二步查看索引名和删除索引。（注意，只能手动删除）

SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

• 建和不建外键约束有什么区别？

---

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

在MySQL中，外键约束会消耗系统资源，大并发SQL操作场景下可能并不适用。像大型网站的中央数据库，就可能因外键约束开销而运行缓慢。因此，MySQL允许不使用自带外键约束，改为在应用层面实现数据一致性检查逻辑。即便不用外键约束，也需通过应用附加逻辑达成外键约束功能，保证数据一致性 。

7. 检查约束（CHECK）

检查约束（CHECK Constraint）能够在插入或更新数据时，对数据进行有效性检查，保证数据符合业务规则。

检查约束是一种规则，通过指定一个布尔表达式，数据库在插入或更新数据时会对该列的值进行检查，如果表达式结果为 true，则允许操作；若为 false，则拒绝操作。

检查约束保证数据的有效性和一致性，确保存储在表中的数据符合特定的业务逻辑或语义规则，防止无效数据进入数据库，提高数据质量。

## MySQL5.7
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)

• check使用举例

age INT CHECK(age > 20),

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

CHECK(height>=0 AND height<3)

8. 默认约束（DEFAULT）

默认约束（Default Constraint）是数据库中用于为表的列提供默认值的一种约束机制。当在插入数据时没有为该列指定具体值时，数据库会自动使用默认约束所定义的值。

默认约束规定了在插入数据时，如果没有为某列提供值，数据库会自动将该列设置为预先定义的默认值。

在插入数据时，无需为所有列都提供值，对于一些具有固定默认值的列，数据库会自动填充，减少了插入数据时的工作量。从而确保表中的每一行数据在某些列上都有合理的值，避免出现空值或不合法的值，提高数据的质量和一致性。

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

## 说明：默认值约束一般不在唯一键和主键列上加

• 建表后

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

alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

8.2 如何删除默认值约束

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. 补充

• 为什么建表时，加 not null default 或 default 0

• 保障数据完整性：not null阻止插入null值，确保每列数据都有确定值。
• 简化数据插入：列有default值，插入语句可省略该列，批量插入或部分列无需赋值时，能提升插入效率。
• 规避业务错误：合理默认值可避免未初始化值引发的业务逻辑错误。

---

直接一点儿说就是不想让表中出现 NULL 值。

• 那么为什么不想要null的值

• 不好比较。null是一种特殊值，比较时只能用专门的 is null 和 is not null 来比较。碰到运算符，通常返回null。
• 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default 或 default 0

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

在MySQL中，AUTO_INCREMENT默认初始值为1，每新增记录，字段值自动加1 。设置该属性时，可指定首条插入记录的自增字段值，后续记录以此为起点递增，例如首条记录指定id值为5，后续id从6开始增加。添加主键约束时，常需设置字段的自动增加属性。

• 外键约束(FOREIGN KEY)能不能跨引擎使用。

外键约束(FOREIGN KEY)不能跨引擎使用。

---

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

---

通过本文的介绍，我们对 MySQL 约束有了较为清晰的认识。从约束的基本概念和分类，到具体约束（如非空约束和唯一性约束）的操作，这些知识对于数据库开发者和管理员来说是非常重要的。合理运用约束可以有效地保障数据库中数据的完整性和准确性，从而提高数据库系统的性能和可靠性。

---