MySQL 约束

约束

约束：作用在表中的字段上，保证字段的数据满足要求，用于保证数据的正确型、有效性、安全性。

分类

约束	作用	关键字
主键	该值必须唯一、不重复、不能为 NULL	PRIMARY KEY
外键	该值必须在指定表中存在	FOREIGN KEY
唯一	该值必须唯一、不重复	UNIQUE
非空	该值不能为空	NOT NULL
默认值	若为指定该值，则使用默认值	DEFAULT
检查	检查字段的值	CHECK

约束代码示例

▼
sql
复制代码
CREATE TABLE users (
    -- 主键，并且自增
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    -- 唯一，并且不能为空
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    -- 检查，年龄必须大于 0 且小于 120
    age INT CHECK ( age > 0 AND age < 120) COMMENT '年龄',
    -- 默认值，默认状态为 1
    status CHAR(1) DEFAULT 1 COMMENT '状态',
    -- 无约束
    gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

外键约束

外键约束只发生在表与表之间，我们可以指定一个字段的值必须存在于另一个表中的某个字段中。比如：现在有一个班级表和学生表，学生表中有一个 class_id 字段，用于保存学生所属的班级的 ID，那么 class_id 字段就可以设置外键约束，保证 class_id 字段保存的班级 ID 必须存在于班级表中的 id 字段中。

添加/修改外键约束

建表时添加外键约束

▼
sql
复制代码
CREATE TABLE student
(
    id       INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name     VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
    class_id INT COMMENT '班级ID',
    CONSTRAINT class_id_keys FOREIGN KEY (id) REFERENCES class (id)
) COMMENT '学生表';

修改表结构时添加外键约束

▼
sql
复制代码
ALTER TABLE student ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id);

删除外键约束

▼
sql
复制代码
ALTER TABLE student DROP FOREIGN KEY fk_student_class_id;

外键约束示例

▼
sql
复制代码
CREATE TABLE student
(
    id       INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name     VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
    class_id INT COMMENT '班级ID'
) COMMENT '学生表';

CREATE TABLE class
(
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名'
) COMMENT '班级表';


INSERT INTO class(name)
VALUES ('一班'),
       ('二班'),
       ('三班'),
       ('四班'),
       ('五班');


-- 没有约束，虽然没有 六班 但是 class_id 为 6 的数据仍可插入
INSERT INTO student(name, class_id)
VALUES ('Tom1', 6);


-- 添加约束
-- 1. 建表时
# CREATE TABLE student
# (
#     id       INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
#     name     VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
#     class_id INT COMMENT '班级ID',
#     CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id) [删除/更新规则]
# ) COMMENT '学生表';

-- 2. 建表后修改
ALTER TABLE student
    ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) [删除/更新规则];

-- 再次添加数据时，就会检查 class_id 的值在 class 表中的 id 字段中是否存在
-- 报错：Cannot add or update a child row: a foreign key constraint fails (`mysql_study`.`student`, CONSTRAINT `fk_student_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
INSERT INTO student(name, class_id)
VALUES ('Tom2', 6);

-- 只有存在才能添加
INSERT INTO student(name, class_id)
VALUES ('Tom3', 5);

-- 因为 student 表中存在 class_id 为 5 的数据
-- 所以当我们要删除 class 表中 id 为 5 的数据时，同样也会报错
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails (`mysql_study`.`student`, CONSTRAINT `fk_student_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
DELETE FROM class WHERE id = 5;

-- 删除外键
ALTER TABLE student DROP CONSTRAINT fk_student_class_id;

外键约束删除规则

删除规则	作用
NO ACTION	默认值，当删除/修改父表的字段值时，会先检查该字段值是否存在外键，如果有则不允许操作
RESTRICT	同 NO ACTION
CASCADE	当删除/修改父表字段值时，会同步删除/修改子表字段值
SET NULL	当删除/修改父表字段值时，会设置子表字段值为 NULL
SET DEFAULT	当删除/修改父表字段值时，会设置子表字段值为默认值(InnoDB 不支持)

外键约束删除规则示例

▼
sql
复制代码
-- 1. 添加外键并制定规则
ALTER TABLE student
    ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) ON UPDATE CASCADE ON DELETE CASCADE;

-- 更新父表数据，子表也会被修改
UPDATE class SET id = 10 WHERE id = 5;

-- 删除父表数据，子表也会被删除
DELETE FROM class WHERE id = 10;


-- 2. 添加外键并制定规则
ALTER TABLE student
    ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) ON UPDATE SET NULL ON DELETE SET NULL;

-- 删除父表的数据，子表中对应的字段值会被设置为 NULL
DELETE FROM class WHERE id = 5;

约束

分类