MySQL中列级约束与表级约束的区别以及两者存在的意义

博主原创文章，转载请说明出处，但是不说我也管不了你🏳️

前言

一直都是对MySQL处于懵懵懂懂的状态，刚刚突然去了解了一下MySQL的约束相关问题，发现MySQL中常用的有六种约束（注：我在8.4版本的手册中还找到了几个约束如：SET、ENUM，但是到底有多少种约束，不知道），分别是：PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK。然后细分又可以分为列级约束和表级约束两大类[1]：

约束 关键字
列级约束 PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK
表级约束 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK

可以看到，表级约束有的，列级约束都有。于是我去搜了一圈关于两种约束有什么区别的文章，最后都是在定义和最终作用上说的，而且在最终作用上只是说列级约束是约束当前列，而表级约束是同时约束多个列，但是我想如果在每个列级约束上加上同一种约束不就可以起到表级约束相同的效果了吗？而且为什么表级约束不能有DEFAULT和NOT NULL？列级约束和表级约束到底约束了个什么？

带着以上疑问，此篇文章诞生。此篇文章也主要解决以上两个问题。

正文

一.什么是约束

“约束”就是使用约定对XX进行束缚，这个“束缚”有很多作用，例如小明老婆生了个儿子，法律规定从此以后他就是这个儿子的爸，这对小明进行了一个身份约束，以后小明儿子学校里打架了，顺着这个身份约束去找小明就对了，然后小明把他儿子框框打一顿，他儿子以后就再也不敢打架了，这便解决了一个问题。

SQL里约束的洋文叫做contraint，SQL 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定(通过CREATE TABLE语句)，也可以在创建表之后规定(通过ALTER TABLE语句)[2]。一句话，SQL中的约束决定了表或列中的数据是否有效。

二.MySQL中的约束

2.1 常用约束

MySQL主要常用的约束有六种，分别是：

①PARIMARY KEY：主键约束。约束某一列或者多列数据值不重复且不为空，注意这里的空指的是NULL，不是指空串，以下文中提到的空都是这个意思。

②FROEIGN KEY：外键约束。针对不同表的列来创建父子列关系约束，约束子列的值取值范围只能在父列已有值的范围下。

③UNIQUE：唯一约束。约束某一列或多列数据值唯一，但是可以有多个空。

④NOT NULL：非空约束，对应NULL空约束。对某一列数据是否能为空做约束。

⑤DEFAULT：默认值约束。对某一列数据做默认值约束，当没有对其赋值时，则使用默认值。

⑥CHECK：值检查约束，MySQL 8.0.16+才支持，之前的版本写了CHECK约束不会报错，同样也不会生效，MySQL只会忽略这个约束[3]。

接下来对这六个约束进行创建演示(为了方便随便建的表，其他的误考虑)，假设某一个班有多个学生，每个学生都已经满18岁，每个学生也都有唯一对应的十位学号，这个班部分学生这学期选修了一门课程（每一门课程也对应一个唯一ID），其余学生则是一门都没有选修，现设计两张表来存储这些学生的信息：

首先是选修课程表elective_course：

MYSQL
1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS elective_course(
    #主键约束，课程ID不能重复且不能为空
	id INT PRIMARY KEY,
    #NOT NULL非空约束，课程名不能为空，CHECK值检查约束，name不能为空串
    name VARCHAR(32) NOT NULL CHECK(name!='')
);

然后是学生表student，包括了学生信息与选课信息：

MYSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS student(
	#主键约束，学生ID不能重复且不能为空
    id INT(10) PRIMARY KEY,
    #非空约束，学生名不能为空，检查约束，学生名不能为空串，唯一约束，学生名不能重复但可以为空
    #注意，以上这种多种约束，是取约束的合集，也就是取最严的那个约束作为最终约束结果【我猜的】
    name VARCHAR(32) NOT NULL CHECK(name!='') UNIQUE,
    #检查约束，年龄必须大于等于18
    age INT CHECK(age>=18),
    #非空约束，课程ID不能为空
    course_id INT NOT NULL,
    #外键约束，课程表中的`id`是指向学生表`course_id`的外键
    FOREIGN KEY(course_id) REFERENCES elective_course(id)
);

2.2 列级约束与表级约束

列级约束：如其名，就是针对某一列的约束；表级约束：如其名，就是针对全表的约束，但是注意这个意思并不是对全表所有的列都有约束，只是设置表约束的时候有个特点，即都是在定义表之后单独设置的表级约束，而列约束一般都是在定义列之后直接可以设置。如下：

MYSQL
1
2
3
4
5
6
#列级约束
CREATE TABLE person (
    . . .
    age INT CHECK (age >= 0),
    . . .
);
MYSQL
1
2
3
4
5
6
7
8
#表级约束
CREATE TABLE qualified_borrowers (
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

所以怎么区分列级约束和表级约束呢？当然不是看约束是在哪定义的，而是结合定义，看约束了多少个列，只约束了一个列那就是列级约束，约束了两个或两个以上就是表级约束。

所以最后的问题，两者的区别是啥以及在使用这两者的时候需要注意什么？但是我搜了一圈，也没看见几个讨论这两者的区别以及注意事项的。要说区别的话，就是字面意思上的区别，一个只约束一列，一个（可以）约束多列（也就是整个表范围），使用时就是根据实际需求自己选是用列约束还是表约束，其他没啥需要注意的[4]。

参考资料

  1. codeacg,MySQL表级约束和列级约束[EB/OL](2021-04-28),https://blog.csdn.net/qq_48759664/article/details/116240961 ↩︎
  2. 菜鸟教程,SQL约束(Constraints)[EB/OL],https://www.runoob.com/sql/sql-constraints.html ↩︎
  3. Praveenkumar Hulakund,MySQL 8.0.16 Introducing CHECK constraint[EB/OL](2019-04-26),https://dev.mysql.com/blog-archive/mysql-8-0-16-introducing-check-constraint/ ↩︎
  4. Tom,Difference between Table level and Column level constraints?[EB/OL](2010-07-26),https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:2665518700346300293 ↩︎