MySQL——约束（constraint）总结

MySQL——约束（constraint）总结

H_Haozi Lv2
  2024-09-01 22:30:24 2024-09-01 22:30:24 Created   2024-12-02 19:32:39 2024-12-02 19:32:39 Updated      

A 引入

A.1约束的含义

一种限制，用于约束表中的数据，用来保证表中的数据的准确和可靠性。MySQL数据库通过约束(constraints)防止无效的数据进入到表中，以保护数据的实体完整性，例如年龄不能为负值，身份证号不能重复等。

A.2 约束的分类

A.2.1 在mysql中，主要有六种约束

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的数据是唯一的，不重复的 DEFAULT
主键约束 主键是一行数据唯一的标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果没有指定该字段的值，则采用默认值 UNIQUE
检查约束（8.0.1.16版本以后） 保证数据满足某个条件 CHECK
外键约束 用来连接两张表之间的数据，保证数据的一致性和完整性 FOREIGN KEY

A.2.2 以上六种约束还能分为两大类

列约束：非空 唯一 默认 主键 检查

表约束：外键 主键 唯一 检查

A.2.3 两类约束的区别

  • 列级约束：

    1. 只能应用于一列上。
    2. 在创建表时，包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔，不必指定列名 。

  • 表级约束：

    1. 可以应用于一列上，也可以应用在一个表中的多个列上。
    2. 在创建表时，与列定义相互独立，不包含在列定义中，与定义用‘，’分隔，且必须指出要约束的列的名称。

A.2.4 举个栗子

  1. 列级约束 
1
2
3
4
5
6
7
8
create table student(
    id int comment '学号' primary key auto_increment ,
    name varchar(20) comment '姓名' not null ,
    age int comment '年龄' check ( age>0 and age <=120 ),
    sex varchar(5) comment '性别' check ( sex = '男' or sex = '女' ),
    email varchar(20) comment '邮箱' unique,
    grade int comment '年级' default 1
)comment '学生信息';

其中约束的关键字直接添加在每行字段末尾,auto_increment是自增关键字
2. 表级约束 

1
2
3
4
5
6
7
8
9
10
11
create table student(
    id int comment '学号' ,
    name varchar(20) comment '姓名' ,
    age int comment '年龄' ,
    sex varchar(5) comment '性别' ,
    email varchar(20) comment '邮箱',
    grade int comment '年级' ,
    constraint p_key1 primary key (id),
    constraint unique1 unique(email),
    constraint check1 check( age>0 and age <=120 )
)comment '学生信息';

其中约束的关键字添加在所有字段创建后的逗号后，使用关键字constraint + 约束关键字

B 细则

B.1 约束的使用

B.1.1 非空约束

例如上面介绍列级约束时创建的name是非空约束

1
name varchar(20) comment '姓名' not null

当我创建一个数据时,加入姓名这一栏输入为null,然后运行

1
2
insert into student (id, name, age, sex, email，grade) 
values (1,null,15,'男','123@hah.com',1);

终端会提示你name不能为null

1
[23000][1048] Column 'name' cannot be null

B.1.2 唯一约束

例如上面介绍列级约束时创建的email是唯一约束 

1
email varchar(20) comment '邮箱' unique

我先创建一个数据，email为123@hah.com

1
2
insert into student (id, name, age, sex, email,grade)  
values (1,'小耗子',15,'男','123@hah.com',1);

当我再创建一个新数据时,email这一栏再次输入为123@hah.com，然后运行

1
2
insert into student (id, name, age, sex, email,grade)  
values (2,'大耗子',16,'男','123@hah.com',1);

终端会提示你email有唯一约束

1
[23000][1062] Duplicate entry '123@hah.com' for key 'student.unique1'

B.1.3 默认约束

例如上面介绍列级约束时创建的grade是默认约束

1
grade int comment '年级' default 1

我创建一个新数据，不填写grade这一栏

1
2
insert into student (id, name, age, sex, email)
values (2,'超级耗子',16,'男','123456@hah.com');

然后使用指令或图形化界面查看这个表（我使用DataGrip图形化查看）

1
select  *from  student;

会发现id为2的学生年级这一栏为默认值1

B.1.4 主键约束

例如上面介绍列级约束时创建的id是主键约束

  • 主键是每一行数据唯一的标识符，通常会加上自增标识符auto_increment
  • 要求id数据非空且唯一
  • 与唯一约束的区别是，唯一约束可以为空，可以有多个，唯一约束一个表中只能有一个

B.1.5 检查约束（8.0.1.16版本以后）

  • 注意MySQL的版本，以前的版本不支持这种约束的写法

例如上面介绍列级约束时创建的age是检查约束

1
age int comment '年龄' check ( age>0 and age <=120 )

当我创建一个数据，将age设置为-1

1
2
insert into student (id, name, age, sex, email,grade)
values (3,'菜狗耗子',-1,'男','1122@hah.com',1);

终端会提示你age不符合条件约束

1
[HY000][3819] Check constraint 'check1' is violated.

B.1.6 外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

  1. 使用背景
    现在创建两张表,并添加部分成员，代码和表格如下图
创建表（student1）
1
2
3
4
5
6
7
create table student1(
    id int comment '学号' ,
    name varchar(20) comment '姓名' ,
    age int comment '年龄' ,
    sex varchar(5) comment '性别' ,
    所属部门id int
)comment '学生信息';
创建表（部门）
1
2
3
4
create table 部门(
    id int ,
    所属部门 varchar(20)
)comment '学生信息';
添加数据
1
2
3
4
5
6
7
8
9
10
11
insert into student1 (id, name, age, sex, 所属部门)
values(1,'小鼠',1,'男',1),
      (3,'小猫',2,'女',1),
      (4,'小狗',3,'男',1),
      (5,'小兔',4,'女',2),
      (6,'小龙',5,'男',3);
      
insert into  部门(id, 所属部门)
values (1,'挖洞部门'),
       (2,'睡觉部门'),
       (3,'干饭部门');
显示两张表
1
2
select  *from student1;
select  *from 部门;


现在对表格2中的挖洞部门执行删除操操作，然后显示这两张表

删除挖洞部门
1
delete from 部门 where id =1;
显示两张表
1
2
select  *from student1;
select  *from 部门;

在逻辑上，表一中属于已经删除的部门1的人仍存在，这边体现了数据的不完整性，但如下图表1却没有发生改变，这个时候外键约束的作用便体现出来了

现在，重新添加挖洞部门,然后设置主键,添加外键，关联这两张表，再次执行删除操作

  • 注意外键中列的数据类型必须和主表主键中对应列的数据类型相同
  • 必须为主表定义主键
添加挖洞部门
1
2
insert into  部门(id, 所属部门)
values (1,'挖洞部门');
添加主键
1
2
3
4
5
6
7
alter table student1
    add constraint student1_pk
        primary key (id);
        
alter table 部门
    add constraint 部门_pk
        primary key (id);
添加外键
1
2
3
4
alter table student1
    add constraint f_key
        foreign key(所属部门)
            references 部门(id);
删除挖洞部门
1
delete from 部门 where id =1;

会发现有报错，不允许删除

1
[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`itone`.`student1`, CONSTRAINT `f_key` FOREIGN KEY (`所属部门`) REFERENCES `部门` (`id`))
  1. 外键约束的删除/更新行为

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

  • 前两种为默认行为
  • 最后一种在MySQL中不支持，仅了解了解
行为 作用
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新(与 RESTRICT 一致) 默认行为
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新(与 NO ACTION 一致)默认行为
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取null)
SET DEEAULT 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)
  1. 添加外键约束 
1
2
3
4
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
#前面的为添加外键的语法，剩下的我们仅需要在后面接上即可
ON UPDATE （CASCADE） ON DELETE （CASCADE）;
#括号里为cascade行为，也可以替换成其他行为

例如下面代码的意思就是:为表student1的字段所属部门添加外键约束，关联表部门的字段id，在更新和删除时都为CASCADE行为

添加外键
1
2
3
4
5
6
alter table student1
    add constraint f_key
        foreign key(所属部门)
            references 部门(id)
            ON UPDATE CASCADE
            ON DELETE CASCADE;
  1. 删除外键约束（因为后面要重新添加外键，并指定删除/更新行为）
删除外键
1
2
alter table student1 drop foreign key f_key;
#f_key是前面对这个外键取的别名，如果不写系统会自动生成一个编号
  1. 演示SET NULL行为
    先将添加外键并将更新/删除行为设置为SET NULL，然后执行删除操作
添加外键并修改删除/更新行为
1
2
3
4
5
6
alter table student1
    add constraint f_key
        foreign key(所属部门)
            references 部门(id)
            ON UPDATE SET NULL
            ON DELETE SET NULL;
删除挖洞部门
1
delete from 部门 where id =1;
显示两张表
1
2
select  *from student1;
select  *from 部门;

会发现原本为部门1的人在表一的部门栏值为null

其它的删除/更新行为就不演示了

B.2 添加约束

B.2.1 在创建表的同时添加约束

例如上面介绍列级约束和表级约束时使用的方法

1
2
3
4
5
6
7
8
create table student(
    id int comment '学号' primary key auto_increment ,
    name varchar(20) comment '姓名' not null ,
    age int comment '年龄' check ( age>0 and age <=120 ),
    sex varchar(5) comment '性别' check ( sex = '男' or sex = '女' ),
    email varchar(20) comment '邮箱' unique,
    grade int comment '年级' default 1
)comment '学生信息';

B.2.2 在创建表之后添加约束

例如上面添加外键约束时

1
2
3
4
5
ALTER TABLE <数据表名> ADD CONSTRAINT <键名> FOREIGN KEY(<列名>);
#例如为name添加唯一约束
alter table student1
    add constraint student1_pk
        unique (name);

B.3 删除约束

例如上面删除外键约束时

1
ALTER TABLE <数据表名> DROP CONSTRAINT <键名>;

B.4 利用可视化工具添加or删除约束（DataGrip）

这个就比较简单直观，直接右键资源管理器的表格，然后修改即可

C 结尾

可恶，第一次写Markdown语言，不熟练

参考文献

  1. 哔哩哔哩视频

留言

有问题请指出,你可以选择以下方式：

  1. 在下方评论区留言
  2. 邮箱留言
  • Title: MySQL——约束（constraint）总结
  • Author: H_Haozi
  • Created at : 2024-09-01 22:30:24
  • Updated at : 2024-12-02 19:32:39
  • Link: https://redefine.ohevan.com/2024/09/01/mysql_constraint/
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐阅读
嵌入式——FreeRTOS操作系统——学习笔记 嵌入式——FreeRTOS操作系统——学习笔记 Qt——写一个自己的串口调试助手 Qt——写一个自己的串口调试助手 HC-05蓝牙模块——学习笔记 HC-05蓝牙模块——学习笔记
推荐阅读
嵌入式——FreeRTOS操作系统——学习笔记 嵌入式——FreeRTOS操作系统——学习笔记 Qt——写一个自己的串口调试助手 Qt——写一个自己的串口调试助手
Comments
On this page
MySQL——约束（constraint）总结
  1. A 引入
    1. A.1约束的含义
    2. A.2 约束的分类
  2. B 细则
    1. B.1 约束的使用
    2. B.2 添加约束
    3. B.3 删除约束
    4. B.4 利用可视化工具添加or删除约束（DataGrip）
  3. C 结尾
    1. 参考文献
    2. 留言