约束可以在创建表时通过 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 nullINSERT INTO student VALUES(2, '李四', '13710011002', null);INSERT INTO student VALUES(2, '李四', null, '110222198912032546');# 错误：Column 'sname' cannot be nullINSERT 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;
ALTER TABLE USER ADD UNIQUE(NAME, PASSWORD);-- 约束名为 uk_name_pwdALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;
# 查看都有哪些约束SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = '表名';# 删除 uk_name_pwd 索引ALTER TABLE USER DROP INDEX uk_name_pwd;# 查看某个表的索引SHOW INDEX FROM 表名;
CREATE TABLE temp( id INT PRIMARY KEY, name VARCHAR(20));
依次插入多条数据：
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 nullINSERT INTO temp VALUES(null, '默烦');
演示创建拥有两个主键约束的表：
1 2 3 4 5
# 错误：Multiple primary key definedCREATE 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));
# 错误：Incorrect table definition; there can be only one auto column and it must be defined as a keyCREATE 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);
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;
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;
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;
# 第一步先查看约束名和删除外键约束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';
在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，使用外键约束可能并不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销使得 SQL 操作效率降低。所以， MySQL 允许不使用系统自带的外键约束，而是在应用层面完成检查数据一致性的逻辑。也就是说，即使不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
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 支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
