MySQL中的约束Constraint解读！

admin • • SQL技巧

MySQL中的约束Constraint解读！

这篇文章详细介绍了MySQL中的各种约束类型,包括非空约束、默认约束、检查约束、唯一约束和主键约束,以及外键约束,每种约束类型都有其特定的用途和语法,并且可以是列级约束或表级约束,文章还探讨了如何给约束命名、删除约束。

约束简介

约束是我们在创建表的时候, 我们可以给表中的字段添加约束确保我们的数据的完整性和有效性, 比如大家平时上网时注册用户常见的 : 用户名不能为空, 对不起, 用户名已经存在等提示信息

约束通常包括下面的这6种

约束类型 约束关键字
非空约束 not null
默认约束 default
检查约束 check
唯一约束 unique
主键约束 primary key
外键约束 foreign key

下面我们会详细的剖析这几种约束(默认约束省略, 就是一个简单的在不插入这个字段的数据时插入默认值)

非空约束

这个约束比较好理解, 就是插入的数据不能为空, 当我们设置这一个约束的时候, 我们使用desc展示表结构的时候, 表的Null那一行就会设置为NO, 允许为空就会为YES, 下面是我们的一个实例

1
2
3
4
5
6
7
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

尝试完成下面的一个需求, 创建一个学校表, 有编号, 学校名称(不能为空), 建校时间, 这时候就需要给name字段加上一个非空约束not null

我们尝试执行下面的SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建一个学校表
create table school(
sno int comment '学校编号',
name varchar(255) not null comment '学校名称',
est_time date comment '建校时间'
);
-- 插入几条数据来进行测试(关于日期类型的插入, 其实底层会进行str_to_date函数的调用, 把字符串转化为日期)
insert into school (sno, name, est_time) values (1, '北京101中学', '1910-08-11');
insert into school (sno, name, est_time) values (2, '南开附中', '1912-04-13');
insert into school (sno, name, est_time) values (3, '清华附中', '1915-03-12');
-- select 查询一下当前的信息
select * from school;
-- 执行结果如下
+------+-----------------+------------+
| sno  | name            | est_time   |
+------+-----------------+------------+
|    1 | 北京101中学     | 1910-08-11 |
|    2 | 南开附中        | 1912-04-13 |
|    3 | 清华附中        | 1915-03-12 |
+------+-----------------+------------+

由于我们添加了非空约束, 也就是我们的name不可以为null, 如果插入一条数据没有name就会报错

1
2
3
4
-- 尝试执行下面的SQL
insert into school (sno, est_time) values (4, '1899-11-06');
-- 会直接报错, 报错信息如下
ERROR 1364 (HY000): Field 'name' doesn't have a default value

检查约束

其实就是在创建一张表的时候添加一定的检查信息, 这个约束时MySQL8版本之后新添加的, 在先前的版本中是不存在的

基础语法

1
2
3
4
create table [表名](
...字段信息
check(约束条件)
);

我们尝试建一张学生表, 要求添加学生的年龄必须大于18岁, 这种情况就可以使用检查约束

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建一张学生表
create table t_stu(
sno int,
name varchar(255),
age int,
check(age > 18)
);
-- 执行DML语句
insert into t_stu(sno, name, age) values (1, 'hh', 19);
insert into t_stu(sno, name, age) values (2, 'xx', 18);
-- 第一条是执行成功的, 但是第二条 18 == 18, 不满足check约束, 所以失败

唯一约束

对一个字段添加unique约束, 这个字段就具有了唯一性, 唯一性的字段是可以为null, 但不可以重复, 如果是null的话, 是可以重复的, 我们拿下面的t_stu表作为一个例子说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 创建了一个学生表, 这个表的email字段是唯一的, 不可以重复
create table t_stu(
no int,
name varchar(255),
email varchar(255) unique
);
# 我们尝试执行下面的DML语句, 执行成功成功的标明 √, 否则为 ×
insert into t_stu (no, name, email) values (1, 'hh', 'hh@163.com');
insert into t_stu (no, name, email) values (2, 'xx', 'xx@163.com');
insert into t_stu (no, name) values (3, 'xx'), (4, 'wx');
# 查看一下当前的信息
select * from t_stu;
+------+------+------------+
| no   | name | email      |
+------+------+------------+
|    1 | hh   | hh@163.com |
|    2 | xx   | xx@163.com |
|    3 | xx   | NULL       |
|    4 | wx   | NULL       |
+------+------+------------+
# 尝试插入一条重复的数据
insert into t_stu (no, name, email) values (5, 'sf', 'hh@163.com');
# 直接报错, 因为email的位置重复了

unique约束也可以和not null 同时使用表示非空且唯一

1
2
3
4
5
create table t_stu(
no int,
name varchar(255),
email varchar(255) not null unique
);

列级约束与表级约束

如果一个约束紧紧的跟在字段后面, 那这个字段仅仅作用于这一个字段, 我们称之为列级约束, 如果一个约束位于表定义的结尾位置, 那么这个约束我们称之为表级约束, 这种约束可以约束多种字段

unique, primary key, foreign key 可以作为表级约束存在, not null 不可以作为表级约束

我们比较一下下面的SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# unique的列级约束
create table t_stu(
no int,
name varchar(255),
email varchar(255),
unique(email)
);
# unique的表级约束
create table t_stu(
no int,
name varchar(255),
email varchar(255),
unique(name,email)
);

那么上述列级和表级的约束有什么区别呢?

答案是 :表级可以联合联合多个字段而列级只能约束一个字段

给约束起名字

约束是以对象的形式存在的, 所有的约束对象对象都存在一个系统表中

information_schema(四个系统数据库之一)中的table_constraints这个表中, 这张表保存的所有的约束名称信息

这里注意, 列级约束是不能起名字的, 但是有系统默认分配的名字, 只有表级别才可以起名字, 如果不起名字的话, 也会自动分配一个

找到约束的名字我们就可以对约束进行删除, 从而消除对某些字段的约束

首先找到这张表

202501130848031

使用这个information_schema数据库

这里面有79张表, 找到这个TABLE_CONSTRAINTS表, 这个表存储的所有的约束对象的信息

desc查看一下表结构, 我们找到刚才的 t_stu 学生表的约束名称

先用DDL语句查看一下创建 t_stu 这张表时的建表语句

我们可以看到, 我们创建表的时候对email字段进行了unique约束, 但是没有给约束起名字, 所以系统会自动分配名字

下面我们查看一下这个约束的名称

这里可以看到这个约束的名称时email

下面我们创建一张新的表, 从新添加一个约束并给约束起名字

基础的语法如下

1
2
3
4
# 表级约束起名的语法
constraint [约束名称] [表级约束的主体];
# 起约束名的标准
表名_约束的字段_约束的简称(unique/pk/fk)

创建一个班级表进行测试

1
2
3
4
5
6
7
-- 创建一个班级表(设置班级编号为主键, 班级名称为唯一键)
create table class(
cno int comment '班级编号',
cname varchar(255) comment '班级名称',
constraint class_cno_pk primary key(cno),
constraint class_cname_unique unique(cname)
);

用上面我们找到约束的名称的流程进行演示…

1
2
3
4
# 使用一下这个系统库
use information_schema;
# 找到class表的约束名称
select constraint_name from table_constraints where table_name = 'class';

pEDtSwq.png

这里可能会有疑问为什么给主键起的名字没有生效呢?

下面是我查阅的资料

MySQL版本限制:从MySQL8.0版本开始，主键的名字不再可以直接修改。这是由于 MySQL的内部存储引擎(如InnoDB)实现方式导致的，InnoDB存储引擎中主键的名字是以索引的形式存储的，修改主键名字实际上是修改索引的名字，会对存储引擎的内部数据结构产生影响，因此MySQL禁止直接修改主键的名字。

但是经过测试, 给外键起名字是生效的

主键约束

主键概念以及注意事项

主键约束是一个比较重要的内容

  1. 主键 : primary key, 简称pk
  2. 主键约束的字段不能为NULL, 并且不可以重复
  3. 任何一张表都应该有主键(第一范式), 没有主键的表可以被视为无效表
  4. 主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
  5. 主键分类：

pEDtpT0.png

单一主键(建议使用这种方式)

1
2
3
4
5
create table t_student(
id bigint primary key,
sno varchar(255) unique,
sname varchar(255) not null
)

联合主键(很少用, 了解)

1
2
3
4
5
6
create table t_user(
no int,
name varchar(255),
age int,
primary key(no,name)
);

主键自增

既然主键值是一个自然的数字，mysql为主键值提供了一种自增机制，不需要我们程序员维护，mysql自动维护该字段键自增

1
2
3
4
create table t_vip(
no int primary key auto_increment,
name varchar(255)
);

外键约束

外键概念以及注意事项

外键约束

  1. 外键约束 : foreign key, 简称fk
  2. 添加了外键约束的字段的数据必须来源于其他的其他字段, 不可以随便设置
  3. 比如我们给a字段添加了外键约束, 要求a字段中的数据必须来源于b字段b字段不一定是主键, 但一定具有唯一性
  4. 外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键 很少用。
  5. 如果a表引用了b表的数据(a是外键, b具有唯一性被引用)
操作类型 执行顺序
创建表时 先创建b, 再创建a
插入数据时 先插入b, 再插入a
删除数据时 先删除a, 再删除b
删除表时 先删除a, 再删除b

外键使用场景

有下面一个需求, 我们想要创建一个学生表, 能够存储学生的信息

我们的第一种设计是这样的

pEDtPYT.png

很明显, 这样创建表的方法会导致数据冗余(实质上是违法了我们第三范式)

这张表是一种典型的一对多的情况, 所以根据创建表的设计(后面会讲)我们创建为两张表

一张学生表, 单独存储学生的信息, 另一张是学校表, 专门存储学校的相关信息, 返回用外键进行关联

pEDtVX9.png
pEDtE6J.png

为什么要设置外键约束, 因为对于学生来说, 学校编号这一个条件不是随便设置的, 要用学校表中的信息进行约束

有了上面的铺垫, 我们尝试创建一个学生表, 和一个学校表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建一个学校表(把学校编号设置为主键)
create table t_school(
sch_no int primary key auto_increment,
sch_name varchar(255) unique
);
-- 创建一个学生表(把学生编号设置为主键, 然后名字添加default约束, 年龄添加check约束, 学校名称为外键)
create table t_student(
stu_no int primary key auto_increment,
stu_name varchar(255) default '无名氏',
age int,
sch_no int,
check(age >= 18),
constraint t_student_sch_no_fk foreign key(sch_no) references t_school(sch_no)
);
-- 插入几条测试数据(按照我们举出来的例子)
insert into t_school (sch_name) values ('清华大学'), ('北京大学'), ('浙江大学'), ('复旦大学');
insert into t_student (stu_name, age, sch_no) values ('hxh', 19, 1), ('dwv', 18, 1), ('cac', 19, 1), ('fqe', 20, 1);
insert into t_student (stu_name, age, sch_no) values ('ger', 18, 2), ('he', 21, 2);
insert into t_student (stu_name, age, sch_no) values ('few', 20, 3), ('rhr', 22, 3), ('wgh', 22, 3);
insert into t_student (stu_name, age, sch_no) values ('rhre', 22, 4), ('wg', 21, 4);

pEDtm01.png

约束的删除与添加

由于我们下面要介绍级联的相关操作, 所以要删除之前的外键约束条件, 我们之前就说过, 可以通过找到约束的名字从而删除约束, 这种操作其实是DDL, 对表层面的一种操作, 我们首先找到t_student表的约束的名字

pEDtMtK.png

删除约束的基础语法如下

1
2
3
4
# 基础语法就是
alter table [表名] drop constraint [约束名称];
# 删除学生表的外键约束
alter table t_student constraint t_student_sch_no_fp;

约束添加的语法与删除的语法类似 , 都是DDL语句那一套逻辑

1
2
3
4
5
# 基础语法
alter table [表名] add constraint [约束名称] [约束主体];
# 比如我们从新把刚才删除外键约束添加回来
alter table t_student add constraint stu_pk
foreign key(sch_no) references t_school(sch_no);

级联相关操作

我们上面介绍外键概念的时候提到过, 如果删除表或者删除表中的数据的时候要注意先后的顺序

那有没有一种方法, 可以在操纵主表的同时, 同时修改副表里面关联的数据呢

级联删除(on delete cascade)

我们从新把上面创建的学校表和学生表拿出来

现在存在外键约束, 我们直接删除学校编号为4的复旦大学是不能操作的, 因为有副表的数据引用

但是通过级联删除就可以实现这一效果(删除主表信息的同时删除副表内容)

基础语法

1
2
-- 只需要在创建约束的时候在末端加上一个 (on delete cascade)
[创建约束主体] on delete cascade;

下面我们进行测试

1
2
3
4
5
6
7
8
-- 首先删除一下之前的外键约束
alter table t_student drop constraint stu_pk;
-- 创建一个新的外键约束(并加上一个级联删除的信息)
alter table t_student add constraint t_student_sch_no_fk foreign key(
sch_no) references t_school(sch_no) on delete cascade;
-- 下面我们从新进行测试
delete from t_school where sch_no = 4;
-- 此时就会显示执行成功, 我们从新查看一下学生表中的数据, 执行结果如下图

注意, 级联删除其实是一个相当危险的操作

级联更新(on update cascade)

作用就是当更新主表数据的同时修改副表中的相关数据

基础语法

1
[约束主体] on update cascade;

下面是测试内容

1
2
3
4
5
6
7
8
9
10
-- 首先还是一下上一个约束
alter table t_student drop constraint t_student_sch_no_fk;
-- 创建一个级联更新约束
alter table t_student add constraint t_student_sch_no_fk
foreign key(sch_no) references t_school(sch_no) on update cascade;
-- 把学校编号为3改为学校编号为5
update t_school set sch_no = 5 where sch_no = 3;
-- 查看一下当前的学生信息
select * from t_student;
-- 执行结果见下图

级联置空(on delete set null)

作用就是当在主表中删除一个数据的时候, 副表中跟这条数据关联的外键位置设置为NULL

基础语法

1
[约束主体] on delete set null;

下面是我们的测试代码

1
2
3
4
5
6
7
8
--还是先删除约束
alter table t_student drop constraint t_student_sch_no_fk;
-- 加一个级联置空的约束
alter table t_student add constraint t_student_sch_no_fk
foreign key(sch_no) references t_school(sch_no) on delete set null;
-- 上一个级联更新我们不是3编号更新为5了么, 我们现在删除5数据对应的编号学校
delete from t_school where sch_no = 5;
-- 执行结果见下图

总结

以上为个人经验，希望能给大家一个参考。

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>