MySQL约束

概述

什么是MySQL约束？

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

约束有什么作用？

保证数据库中数据的正确、有效性和完整性。

分类

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本 之后)	保证字段值满足某一个条件	CHECK
检查约束(8.0.16版本 之后)	保证字段值满足某一个条件	CHECK

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
案例
案例需求： 根据需求，完成表结构的创建。需求如下：

字段名	字段含 义	字段类型	约束条件	约束关键字
id	ID唯一 标识	int	主键，并且自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不为空，并且唯一	NOT NULL , UNIQUE
age	年龄	int	大于0，并且小于等 于120	CHECK
status	状态	char(1)	如果没有指定该值， 默认为1	DEFAULT
gender	性别	char(1)	无

创建表SQL

create table tb_user(
  id int primary key auto_increment comment 'ID唯一标识',
  name varchar(10) not null unique comment  '姓名',
  age int check ( age > 0 and age < 120 ) comment '年龄',
  status char(1) default '1' comment '状态',
  gender char(1) comment '性别'
) comment '用户表';

验证约束是否生效。

验证主键自增。

insert into tb_user values (null,'张三',23,1,'男'), (null,'李四',24,0,'男');

可以看见插入数据时，并未设置ID值，ID已成功自增。

验证主键唯一。

insert into tb_user values (1,'王五',25,1,'男');

可以看见插入失败，提示错误：[23000][1062] Duplicate entry '1' for key 'tb_user.PRIMARY'。

验证姓名唯一。

insert into tb_user values (null,'张三',25,1,'男');

可以看见插入失败，提示错误：[23000][1062] Duplicate entry '张三' for key 'tb_user.name'。

验证年龄范围。

insert into tb_user values (null,'王五',125,1,'男');

insert into tb_user values (null,'王五',-12,1,'男');

可以看见插入失败，提示错误：[HY000][3819] Check constraint 'tb_user_chk_1' is violated.。

验证默认值。

insert into tb_user( name, age, gender) values ('王五',25,'男');

刷新数据表，可以看见status字段有默认值。

外键约束

外键又称外部键，是指在关系数据库中，每个数据表都是由关系来连系彼此的关系，父数据表（Parent Entity）的主键（Primary Key）会放在另一个数据表，当做属性以创建彼此的关系，而这个属性就是外键。
外键是表中的一组特性，它引用另一个表的主键。外键链接这两个表。另一种说法：在关系数据库的上下文中，外键是一组受某种包含依赖约束的特性，特别是由一个关系R中的外键特性组成的元组必须也存在于其他一些（不一定是不同的）关系S中，而且这些属性也必须是S中的候选键。简而言之，外键是一组引用候选键的特性。

外键有什么作用？
可以用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

由于至少需要两张数据表才能设置外键，因此先创建两张数据表，并添加数据。

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');
create table emp
(
    id         int auto_increment comment 'ID' primary key,
    name       varchar(50) not null comment '姓名',
    age        int comment '年龄',
    job        varchar(20) comment '职位',
    salary     int comment '薪资',
    entry_date date comment '入职时间',
    manager_id int comment '直属领导ID',
    dept_id    int comment '部门ID'
) comment '员工表';
INSERT INTO emp (id, name, age, job, salary, entry_date, manager_id, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

查看创建后的表。

由于现在并未创建外键，所以这时两张表是相互独立的。若删除部门表中的记录，并不会影响到员工表。

-- 删除部门表中研发部
delete from dept where id = 1;

可以看见删除部门数据后，员工表并无任何变化。

添加外键

添加外键可以在创建表的时候添加，也可以在创建表后添加。

-- 创建表时添加外键
CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

在创建表时，添加外键。
为了演示，将刚才创建的表删除。

drop table if exists emp;
drop table if exists dept;

可以看见表已经删除了。
接下来在创建表格时，添加外键约束。

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
create table emp
(
    id         int auto_increment comment 'ID' primary key,
    name       varchar(50) not null comment '姓名',
    age        int comment '年龄',
    job        varchar(20) comment '职位',
    salary     int comment '薪资',
    entry_date date comment '入职时间',
    manager_id int comment '直属领导ID',
    dept_id    int comment '部门ID',
    constraint foreign key fk_emp_dept_id (dept_id) references dept (id)
) comment '员工表';

此时可以看见员工表中dept_id字段上有了一把蓝色的钥匙，这个标识在DataGrip中表示外键。

在以下SQL前，再次将表删除，并使用最初的SQL创建表，并添加数据。

-- 在已经创建好的表上添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

alter table emp add constraint fk_emp_dept foreign key (dept_id) references dept(id);

可以看见这种方式也可以创建外键。

再次测试删除数据。

delete from dept where id = 1;

在外键约束的作用下，此时已经不能随意删除被约束的字段，对表中数据起到一定的保护作用。

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

alter table emp drop  foreign key fk_emp_dept;

此时已删除外键。

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行
为有以下几种:

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为。
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为。
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则 也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表 中该外键值为null（这就要求该外键允许取null）。
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

具体语法为:

ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 
		主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE、SET NULL。

CASCADE

-- CASCADE
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id) on update cascade on delete cascade;

修改父表id为1的记录，将id修改为6。

update dept set id = 6 where id = 1;

可以发现，原来在子表中dept_id值为1的记录，现在也变为6了，这就是cascade级联的效果。

删除父表id为6的记录。

delete from dept where id = 6;

SET NULL

演示之前，先删除两张表，再通过SQL语句重新恢复数据。

alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id) on update set null on delete set null;

接下来，我们删除id为1的数据。

delete from dept where id = 1;

可以发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。
这就是SET NULL这种删除/更新行为的效果。