红茶的个人站点

  • 首页
  • 专栏
  • 开发工具
  • 其它
  • 隐私政策
Awalon
Talk is cheap,show me the code.
  1. 首页
  2. 专栏
  3. MySQL学习笔记
  4. 正文

MySQL 从入门到精通 3:约束

2025年7月31日 16点热度 0人点赞 0条评论

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。

参考资料

  • 黑马程序员 MySQL数据库入门到精通

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL 外键 约束
最后更新:2025年7月31日

魔芋红茶

加一点PHP,加一点Go,加一点Python......

点赞
< 上一篇

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复

COPYRIGHT © 2021 icexmoon.cn. ALL RIGHTS RESERVED.
本网站由提供CDN加速/云存储服务

Theme Kratos Made By Seaton Jiang

宁ICP备2021001508号

宁公网安备64040202000141号