MySQL中的约束Constraint解读!
MySQL中的约束Constraint解读!
约束简介
约束是我们在创建表的时候, 我们可以给表中的字段添加约束确保我们的数据的完整性和有效性, 比如大家平时上网时注册用户常见的 : 用户名不能为空, 对不起, 用户名已经存在等提示信息
约束通常包括下面的这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这个表中, 这张表保存的所有的约束名称信息
这里注意, 列级约束是不能起名字的, 但是有系统默认分配的名字, 只有表级别才可以起名字, 如果不起名字的话, 也会自动分配一个
找到约束的名字我们就可以对约束进行删除, 从而消除对某些字段的约束
首先找到这张表
使用这个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' ; |
这里可能会有疑问为什么给主键起的名字没有生效呢?
下面是我查阅的资料
MySQL版本限制:从MySQL8.0版本开始,主键的名字不再可以直接修改。这是由于 MySQL的内部存储引擎(如InnoDB)实现方式导致的,InnoDB存储引擎中主键的名字是以索引的形式存储的,修改主键名字实际上是修改索引的名字,会对存储引擎的内部数据结构产生影响,因此MySQL禁止直接修改主键的名字。
但是经过测试, 给外键起名字是生效的
主键约束
主键概念以及注意事项
主键约束是一个比较重要的内容
- 主键 : primary key, 简称pk
- 主键约束的字段不能为NULL, 并且不可以重复
- 任何一张表都应该有主键(第一范式), 没有主键的表可以被视为无效表
- 主键值是这行记录的身份证号,是唯一标识。在数据库表中即使两条数据一模一样,但由于主键值不同,我们也会认为是两条完全的不同的数据。
- 主键分类:
单一主键(建议使用这种方式)
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) ); |
外键约束
外键概念以及注意事项
外键约束
- 外键约束 : foreign key, 简称fk
- 添加了外键约束的字段的数据必须来源于其他的其他字段, 不可以随便设置
- 比如我们给a字段添加了外键约束, 要求a字段中的数据必须来源于b字段b字段不一定是主键, 但一定具有唯一性
- 外键约束可以给单个字段添加,叫做单一外键。也可以给多个字段联合添加,叫做复合外键。复合外键 很少用。
- 如果a表引用了b表的数据(a是外键, b具有唯一性被引用)
操作类型 | 执行顺序 |
---|---|
创建表时 | 先创建b, 再创建a |
插入数据时 | 先插入b, 再插入a |
删除数据时 | 先删除a, 再删除b |
删除表时 | 先删除a, 再删除b |
外键使用场景
有下面一个需求, 我们想要创建一个学生表, 能够存储学生的信息
我们的第一种设计是这样的
很明显, 这样创建表的方法会导致数据冗余(实质上是违法了我们第三范式)
这张表是一种典型的一对多的情况, 所以根据创建表的设计(后面会讲)我们创建为两张表
一张学生表, 单独存储学生的信息, 另一张是学校表, 专门存储学校的相关信息, 返回用外键进行关联
为什么要设置外键约束, 因为对于学生来说, 学校编号这一个条件不是随便设置的, 要用学校表中的信息进行约束
有了上面的铺垫, 我们尝试创建一个学生表, 和一个学校表
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); |
约束的删除与添加
由于我们下面要介绍级联的相关操作, 所以要删除之前的外键约束条件, 我们之前就说过, 可以通过找到约束的名字从而删除约束, 这种操作其实是DDL, 对表层面的一种操作, 我们首先找到t_student表的约束的名字
删除约束的基础语法如下
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
欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群,共享资源。
5、优惠的会员商品。
6、一次付费只需129元,即可下载本站文章涉及的文件和软件。
共有 0 条评论