SQL基础⑪ | 约束

0 序言

本文主要围绕数据库约束讲起，涵盖约束的基本概念、分类及各类约束（非空、唯一、主键、自增列、外键、CHECK、DEFAULT）的作用、特点、操作方法与示例。

通过学习，可掌握约束的定义逻辑、使用场景及实操技能，理解其在保证数据完整性中的关键作用。

1 约束（constraint）概述

1.1 为什么需要约束

从数据完整性来说，

• 数据完整性（Data Integrity）指数据的精确性（Accuracy）和可靠性（Reliability），目的是防止数据库中存在不符合语义规定的数据，避免错误信息导致无效操作或错误。

我们可以从四方面保证数据完整性：

• 实体完整性：如同一表中不能有两条完全相同无法区分的记录。
• 域完整性：如年龄范围0-120，性别范围“男/女”。
• 引用完整性：如员工所在部门需在部门表中存在。
• 用户自定义完整性：如用户名唯一、密码不能为空等。

1.2 什么是约束

约束是表级的强制规定，可在创建表时（通过CREATE TABLE语句）或表创建后（通过ALTER TABLE语句）设置。

1.3 约束的分类

1.3.1 按约束数据列的限制

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

1.3.2 按约束的作用范围

• 列级约束：作用于单个列，跟在列定义后。
  • 支持的约束类型：语法上都支持，但外键无效果。
  • 是否可起约束名：不可以。
• 表级约束：作用于多个列，单独定义在所有列下方。
  • 支持的约束类型：默认和非空不支持，其他支持。
  • 是否可起约束名：可以（主键无效果）。

1.3.3 按约束的作用

• NOT NULL：非空约束，规定字段不能为空。
• UNIQUE：唯一约束，规定字段在表中唯一。
• PRIMARY KEY：主键约束（非空且唯一）。
• FOREIGN KEY：外键约束。
• CHECK：检查约束。
• DEFAULT：默认值约束。

1.4 查看表的约束

通过查询系统库信息查看：

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

这里就可以看到表约束详情。

2 非空约束（NOT NULL）

2.1 作用

限定某个字段/列的值不允许为空。
关键字为：NOT NULL

2.2 特点

• 默认情况下，所有类型的值都可为NULL（包括INT、FLOAT等）。
• 非空约束仅作用于单个列，一个表可有多列设非空约束。
• 空字符串’'≠NULL，0≠NULL。

2.3 添加非空约束

2.3.1 建表时添加

CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型 NOT NULL,字段名 数据类型 NOT NULL
);-- 示例
CREATE TABLE emp(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
);

• 插入数据示例：

-- 成功（所有非空字段均赋值）
INSERT INTO student VALUES(1,'张三','13710011002','110222198912032545');
-- 失败（cardid为NULL，违反非空约束）
INSERT INTO student VALUES(2,'李四','13710011002',NULL);
-- 成功（tel允许为空）
INSERT INTO student VALUES(2,'李四',NULL,'110222198912032546');
-- 失败（sname为NULL，违反非空约束）
INSERT INTO student VALUES(3,NULL,NULL,'110222198912032547');

可以自己动手试一下，

规则出错会弹出错误信息，

但只要满足建表是设定好的约束条件即可。

2.3.2 建表后添加

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;-- 示例
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;
ALTER TABLE student MODIFY sname VARCHAR(20) NOT NULL;

这样就可以在已有的表格进行约束添加。

2.4 删除非空约束

-- 方式1：显式指定NULL
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
-- 方式2：不指定NOT NULL（默认允许为空）
ALTER TABLE 表名称 MODIFY 字段名 数据类型;-- 示例
ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;
ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3 唯一约束（UNIQUE）

限制某个字段/列的值在整个表中唯一。

这里核心就是唯一，关键字为：UNIQUE

3.1 特点

• 一个表可有多个唯一约束。
• 可约束单个列或多列组合（复合唯一）。
• 允许列值为空（可多个NULL）。
• 未命名时，默认与列名相同（单列）或与组合列中第一个列名相同（多列）。
• MySQL会为唯一约束列自动创建唯一索引。

3.2 添加唯一约束

3.2.1 建表时添加

-- 列级约束方式
CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型 UNIQUE,字段名 数据类型 UNIQUE KEY
);-- 表级约束方式
CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型,字段名 数据类型,CONSTRAINT 约束名 UNIQUE KEY(字段名)
);-- 示例
CREATE TABLE student(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)
);

• 插入数据示例（违反唯一约束）：

-- 成功
INSERT INTO student VALUES(1,'张三','13710011002','101223199012015623');
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');
-- 失败（cardid重复）
INSERT INTO student VALUES(3,'王五','13710011004','101223199012015624');
-- 失败（tel重复）
INSERT INTO student VALUES(3,'王五','13710011003','101223199012015625');

后两个语句就会报错，为什么呢？

就是因为它违反了唯一约束，所以系统就会报错。

3.2.2 建表后添加

-- 方式1：表级约束添加
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);
-- 方式2：列级约束添加
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;-- 示例
ALTER TABLE USER ADD UNIQUE(NAME,PASSWORD);
ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;-- 为student表的tel和cardid添加唯一约束
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 复合唯一约束

• 多列组合的值唯一，语法：

CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型,字段名 数据类型,UNIQUE KEY(字段列表) -- 字段间用逗号分隔
);-- 示例（选课表：学生ID和课程ID组合唯一，避免重复选课）
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');
INSERT INTO student_course VALUES(1,1,1001,89),(2,1,1002,90),(3,2,1001,88),(4,2,1002,56);-- 失败（sid=1和cid=1001的组合已存在）
INSERT INTO student_course VALUES(5,1,1001,88);

3.4 删除唯一约束

• 需通过删除唯一索引实现，步骤：

-- 1. 查看约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
-- 2. 删除唯一索引（索引名即约束名）
ALTER TABLE 表名称 DROP INDEX 约束名;-- 示例
ALTER TABLE USER DROP INDEX uk_name_pwd;-- 查看表索引（辅助确认）
SHOW INDEX FROM 表名称;

比如说这张图片，就能看到具体的约束了。

再比如说，我现在想要删除tel这个约束，

运行程序后，

4 主键约束（PRIMARY KEY）

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

关键字为：PRIMARY KEY

4.1 特点

• 相当于唯一约束+非空约束：不允许重复，不允许为空。
• 一个表最多只能有一个主键约束（可单列或多列组合）。
• 复合主键：多列组合唯一，且各列均不允许为空。
• MySQL主键名固定为PRIMARY，自定义名称无效。
• 系统会为-primary key约束列创建主键索引，删除主键约束时索引自动删除。
• 不建议修改主键值，可能破坏数据完整性。

4.2 添加主键约束

4.2.1 建表时添加

-- 列级约束方式
CREATE TABLE 表名称(字段名 数据类型 PRIMARY KEY,字段名 数据类型
);-- 表级约束方式
CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型,CONSTRAINT 约束名 PRIMARY KEY(字段名)
);-- 示例
CREATE TABLE temp(id INT PRIMARY KEY,name VARCHAR(20)
);-- 列级约束+自增
CREATE TABLE emp4(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
);-- 表级约束示例
CREATE TABLE emp5(id INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20),pwd VARCHAR(15),CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

• 插入数据示例（违反主键约束）：

-- 成功
INSERT INTO temp VALUES(1,'张三'),(2,'李四');
-- 失败（id=1重复）
INSERT INTO temp VALUES(1,'王五');
-- 失败（id为NULL）
INSERT INTO temp VALUES(NULL,'李琦');

4.2.2 建表后添加

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); -- 字段列表可为单列或多列（复合主键）-- 示例
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd); -- 复合主键

4.3 复合主键

• 多列组合唯一标识记录，语法：

CREATE TABLE 表名称(字段名 数据类型,字段名 数据类型,字段名 数据类型,PRIMARY KEY(字段名1,字段名2)
);-- 示例（选课表：sid和cid组合为主键）
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) -- 复合主键
);-- 插入数据
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);-- 失败（sid=1和cid=1001的组合已存在）
INSERT INTO student_course VALUES(1,1001,100);

道理相同，

这里就不重复演示了。

4.4 删除主键约束

ALTER TABLE 表名称 DROP PRIMARY KEY;-- 示例
ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE emp5 DROP PRIMARY KEY;

这里要注意，删除后，非空约束可能仍存在（若原主键列有非空设置）。

5 自增列（AUTO_INCREMENT）

使某个字段的值自动递增。

关键字为：AUTO_INCREMENT

5.1 特点和要求

• 一个表最多一个自增列。
• 自增列必须是键列（主键或唯一键）。
• 数据类型必须为整数类型。
• 若指定0或NULL，值为当前最大值+1；手动指定具体值则直接赋值。

5.2 添加自增约束

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

5.2.2 建表后添加

ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;-- 示例
CREATE TABLE employee(eid INT PRIMARY KEY,ename VARCHAR(20)
);
ALTER TABLE employee MODIFY eid INT AUTO_INCREMENT;

5.3 删除自增约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型; -- 移除AUTO_INCREMENT-- 示例
ALTER TABLE employee MODIFY eid INT;

5.4 MySQL 8.0特有-自增变量的持久化

• MySQL 5.7及之前：自增主键值若大于max(primary key)+1，重启后会重置为max(primary key)+1，可能导致冲突。
• MySQL 8.0：将自增计数器持久化到重做日志，重启后根据日志初始化，避免重置。
• 示例（MySQL 5.7与8.0对比）：

-- 创建表
CREATE TABLE test1(id INT PRIMARY KEY AUTO_INCREMENT);
-- 插入数据
INSERT INTO test1 VALUES(0),(0),(0),(0); -- id为1,2,3,4
DELETE FROM test1 WHERE id=4; -- 删除id=4
INSERT INTO test1 VALUES(0); -- MySQL 5.7: id=5；8.0: id=5
DELETE FROM test1 WHERE id=5;
-- 重启数据库后插入
INSERT INTO test1 VALUES(0); -- MySQL 5.7: id=4；8.0: id=6

从这里对比可以看出，

MySQL 5.7 与 8.0 自增主键机制是不相同的，

5.7 中自增计数器存内存，重启后重置为表最大主键 +1，可能致主键不连续、引发冲突；

8.0 则将其持久化到重做日志，重启恢复状态保连续性。

6 外键约束（FOREIGN KEY）

保证某个表的字段引用的完整性（如员工部门需在部门表中存在）。

关键字为FOREIGN KEY

6.1 主表和从表（父表和子表）

• 主表（父表）：被引用的表。
• 从表（子表）：引用主表的表。

比如说，部门表是主表，员工表是从表；学生表和课程表是主表，选课表是从表。

6.2 特点

• 从表外键列必须引用主表的主键或唯一约束列（被引用值需唯一）。
• 未命名时，默认生成外键名（如student_ibfk_1），可自定义。
• 创建顺序：先主表，后从表；
• 删除顺序：先从表（或先删外键约束），后主表。
• 主表记录被从表引用时，不可直接删除（需先删从表依赖数据）。
• 一个表可建立多个外键约束。
• 从表外键列与主表被引用列名可不同，但数据类型和逻辑意义必须一致。

6.3 添加外键约束

6.3.1 建表时添加

-- 创建主表
CREATE TABLE 主表名称(字段1 数据类型 PRIMARY KEY,字段2 数据类型
);-- 创建从表（含外键）
CREATE TABLE 从表名称(字段1 数据类型 PRIMARY KEY,字段2 数据类型,CONSTRAINT 外键约束名 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, -- 部门ID（外键）FOREIGN KEY (deptid) REFERENCES dept(did)
);

6.3.2 建表后添加

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段) [ON UPDATE 等级] [ON DELETE 等级];-- 示例
ALTER TABLE emp ADD CONSTRAINT emp_dept_id_fk FOREIGN KEY(deptid) REFERENCES dept(did);

6.4 常见错误情况

• 主表被引用列非键列：ERROR 1215 (HY000): Cannot add foreign key constraint
• 数据类型不一致：ERROR 1215 (HY000): Cannot add foreign key constraint
• 从表插入主表不存在的值：ERROR 1452 (23000): ... foreign key constraint fails

6.5 约束等级

• Cascade：主表更新/删除，从表同步更新/删除匹配记录。
• Set null：主表更新/删除，从表匹配记录列设为NULL（外键列不能为NOT NULL）。
• No action/Restrict：若从表有匹配记录，不允许主表更新/删除（默认等级）。
• Set default：主表变更，从表外键列设为默认值（InnoDB不支持）。
• 示例（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
);-- 插入数据
INSERT INTO dept VALUES(1001,'教学部'),(1002,'财务部'),(1003,'咨询部');
INSERT INTO emp VALUES(1,'张三',1001),(2,'李四',1001),(3,'王五',1002);-- 主表更新，从表同步（deptid=1002→1004，emp中王五的deptid变为1004）
UPDATE dept SET did=1004 WHERE did=1002;-- 主表删除，从表设为NULL（删除deptid=1001，emp中张三、李四的deptid变为NULL）
DELETE FROM dept WHERE did=1001;

前面的4 5小结，理解难度不算很高，

思路也比较清晰，

这里的示例我用图片给大家解释一下，

这个理解了，后续的7、8小结理解起来会更清洗，也更容易一些。

6.6 删除外键约束

-- 1. 查看外键约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '从表名';
-- 2. 删除外键约束
ALTER TABLE 从表名 DROP FOREIGN KEY 约束名;
-- 3. 查看索引名（外键自动创建的索引）
SHOW INDEX FROM 从表名;
-- 4. 删除索引
ALTER TABLE 从表名 DROP INDEX 索引名;-- 示例
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;
ALTER TABLE emp DROP INDEX deptid;

6.7 开发场景与规范

• 非必须建立外键：外键会增加系统开销，高并发场景可能不适用，可在应用层保证数据一致性。

7 CHECK 约束

检查字段值是否符合指定条件（如范围、枚举等）。

关键字为：CHECK

7.1 示例

-- MySQL 8.0有效
CREATE TABLE employee(eid INT,ename VARCHAR(5),gender CHAR CHECK (gender IN ('男','女')) -- 性别只能是男/女
);CREATE TABLE temp(id INT AUTO_INCREMENT,NAME VARCHAR(20),age INT CHECK(age > 20), -- 年龄大于20PRIMARY KEY(id)
);

8 DEFAULT 约束

为字段指定默认值，插入数据时未显式赋值则使用默认值。

关键字为：DEFAULT

8.1 添加默认值约束

8.1.1 建表时添加

CREATE TABLE 表名称(字段名 数据类型 PRIMARY KEY,字段名 数据类型 NOT NULL DEFAULT 默认值,字段名 数据类型 DEFAULT 默认值
);-- 示例
CREATE TABLE employee(eid INT PRIMARY KEY,ename VARCHAR(20) NOT NULL,gender CHAR DEFAULT '男', -- 默认性别为男tel CHAR(11) NOT NULL DEFAULT '' -- 默认电话为空字符串
);

8.1.2 建表后添加

ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值;
-- 保留非空约束时
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL;-- 示例
CREATE TABLE employee(eid INT PRIMARY KEY,ename VARCHAR(20),gender CHAR,tel CHAR(11) NOT NULL
);
-- 为gender添加默认值
ALTER TABLE employee MODIFY gender CHAR DEFAULT '男';
-- 为tel添加默认值并保留非空
ALTER TABLE employee MODIFY tel CHAR(11) DEFAULT '' NOT NULL;

8.2 删除默认值约束

-- 移除DEFAULT，保留非空
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
-- 移除DEFAULT，允许为空
ALTER TABLE 表名称 MODIFY 字段名 数据类型;-- 示例
ALTER TABLE employee MODIFY gender CHAR; -- 移除gender的默认值
ALTER TABLE employee MODIFY tel CHAR(11) NOT NULL; -- 移除tel的默认值，保留非空

9 小结

本文详细介绍了数据库约束的核心知识，包括约束的定义、分类及各类约束（非空、唯一、主键、自增、外键、CHECK、DEFAULT）的操作方法与示例。

约束是保证数据完整性的关键机制，通过合理使用约束，可有效防止无效或错误数据进入数据库。

不同约束适用于不同场景，如主键用于唯一标识记录，外键保证引用完整性，默认值简化数据插入。

同时，需注意数据库版本差异（如MySQL 8.0自增持久化）及开发规范（如阿里不建议使用外键），在实际应用中灵活选择合适的约束策略。