MySQL 包含以下字段约束:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 数据不能为null | NOT NULL |
唯一约束 | 字段的值唯一,不能重复 | UNIQUE |
主键约束 | 唯一确定表中的一行数据 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定,使用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证插入数据时满足约束条件 | CHECK |
外键约束 | 保证两张表数据关联的一致性和完整性 | FOREIGN KEY |
假设有一个建表需求:
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
---|---|---|---|---|
id | ID唯一标识 | int | 主键,且自增 | PRIMARY KEY,AUTO_INCREMENT |
name | 姓名 | varchar(10) | 不为空且唯一 | NOT NUL,UNIQUE |
age | 年龄 | int | 0~120之间 | CHECK |
status | 状态 | char(1) | 默认值1 | DEFAULT |
gender | 性别 | char(1) | 无 |
对应的建表语句:
create table user(
id int unsigned primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int unsigned check ( age>=0 and age<=120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '用户表';
插入一条数据:
insert into user(name, age, status, gender)
values ('Tom1', 20, '1', '男');
可以观察到主键是自增的。
如果试图添加 name 为 null 的数据:
insert into user(name, age, status, gender)
values (null, 20, '1', '男');
会报错,因为有非空约束。
同样的,也不能插入已经存在的 name 数据:
insert into user(name, age, status, gender)
values ('Tom1', 20, '1', '男');
因为有唯一约束。
插入数据的年龄范围不能超过 CHECK 约束的定义:
insert into user(name, age, status, gender)
values ('Tom3', 125, '1', '男');
会报错。
如果没有指定 status 字段的值,会使用默认值:
insert into user(name, age, gender)
values ('Tom3', 66, '男');
外键约束
假设有一张部门表:
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 '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, 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);
员工表通过字段dept_id
关联部门表,但是没有显式的设置外键约束。在这种情况下,如果删除部门表中相关的数据行,比如 id 为 1 的研发部,会影响到数据的完整性和一致性。
为 emp 表的 dept_id 字段添加外键约束:
alter table emp add constraint fk_dept foreign key (dept_id) references dept(id);
现在再删除 dept 表的有关联的数据行,就会触发报错。如果一定要进行删除,需要先删除外键约束:
alter table emp drop foreign key fk_dept;
外键的更新/删除行为:
行为 | 说明 |
---|---|
NO ACTION | 当主表删除/更新对应记录时,检查是否有对应外键,如果有,不允许删除/更新。 |
RESTRICT | 与 NO ACTION 行为一致。 |
CASCADE | 当主表删除/更新对应记录时,对从表外键相同的数据行也进行删除/更新。 |
SET NULL | 当主表删除对应记录时,将从表外键相同的数据行的外键设置为 null(如果允许为 null) |
SET DEFAULT | 当主表变更时,从表外键设置为默认值(innodb 不支持) |
添加外键,并将更新/删除行为设置为 CASCADE:
alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
此时如果将 dept 表中的 id 为 1 的行的 id 变更为 6,就能看到 emp 表中相关行的外键 dept_id 也一起变为 6。
同样的,如果删除 id 为 6 的行,emp 表中关联的数据行也会被一同删除。
重置两张表,并添加一个使用 SET NULL 行为的外键:
alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update set null on delete set null ;
删除 dept 表中的数据行后,可以观察到 emp 表中对应的数据行的外键字段 dept_id 被设置为 null。
文章评论