mysql 各种约束的区别_不是吧，阿Sir，MySQL常见约束你竟然还不知道吗？

以前写的太乱了，翻出来重新整理下

小系列目录：

(一) MySQL入门，问题不大

(一) 引入约束

(1) 约束出现在哪里？

想要讲解约束，就要知道约束用在哪里，用来干嘛？

SQL 语言通过定义一个关系所对应的基本表来完成关系模式的定义，其语句格式为：

CREATE TABLE 表名(

[],

[ [],...],

[]

);

符号规定：下面展示一些定义的时候，为简便理解，使用中文配合符号表述(会有具体举例，不用担心理解不了)

<> 中的内容为实际的语义

[] 中的内容为任选项(不填写也可)

{} 中的内容必须显式的指定

| 为选项符

[,...n] 表示前面的项可以重复多次

(2) 约束用来干嘛？

约束，就是针对属性值的一些约束条件，只针对某一列，叫做列级约束、针对多列属性的约束，叫做表级约束

怎么理解呢？就例如某一列叫做 学号，我们就指定约束，这一行不允许为 NULL ，同时我们还能指定它为主键，这样通过学号就可以查找到一条唯一的学生记录了，还有例如外键知识等等...

总结起来就一句话：约束用来对表中的数据进行限定，保证数据的正确性、有效性和完整性

同样，有了约束知识的铺垫，我们就可以引申出后面的一些知识，例如多表操作等等，所以约束虽然简单，还是非常重要的哈~

(二) 常见约束

(1) 主键约束

A：基本概念

在关系模型中，主键的本质其实就是一个候选键

理解非常简单，就是能通过这个主键，确定一个唯一的记录：例如学号是学生实体的候选键，一个学号就能确定这个学生到底哪个学生，而我们不选择姓名，这是因为，姓名在实际的情况中，不能作为一个唯一的标识，确认一个唯一的学生记录

候选键：关系中能唯一标志一个元组的最小属性集

B：特点

确定为主键的列，不能为空，也不能重复！！！

C：具体操作

指定主键约束，使用的是 PRIMARY KEY 关键字

一般来说，主键约束主要用在创建表时，指定约束的方式有两种：

① 定义在列后

CREATE TABLE students (

sid INT(8) PRIMARY KEY,

sname VARCHAR(5),

department VARCHAR(32),

birthday date

)

② 独立定义

CREATE TABLE students (

sid INT(8),

sname VARCHAR(5),

department VARCHAR(32),

birthday date,

PRIMARY KEY (sid)

)

如果在表已经创建好的前提下，还可以通过下列两种方式进行主键的指定和删除

① 删除主键

ALTER TABLE students DROP PRIMARY KEY;

② 指定主键

ALTER TABLE students ADD PRIMARY KEY(sid);

ALTER TABLE students MODIFY sid INT PRIMARY KEY;

D：主键自增

提到主键，就必须提到主键自增了，这个功能也是非常常用的，当设置主动自增后，例如你使用高级语言，操作数据库，向学生表插入一条记录后，即使不给出主键值，主键值也会自动生成出来，并且会在最大主键值的基础上 + 1，例如 0，1，2 ... ，n

最重要的一点，主键必须是整型，才能实现自增喔~

如果主键例如 sid 为 varchar 类型，就会有这样的报错：Incorrect column specifier for column 'sid'

同样，主键自增一般用在创建表的时候，使用 AUTO_INCREMENT，直接跟在列名后即可

CREATE TABLE students (

sid INT(8) PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(5),

department VARCHAR(32),

birthday date

)

如果表已经创建好了，还可以进行是否自增的修改

① 设置主键自增

ALTER TABLE students CHANGE sid sid INT AUTO_INCREMENT;

ALTER TABLE students MODIFY sid INT AUTO_INCREMENT;

② 删除主键自增

ALTER TABLE students CHANGE sid sid INT;

ALTER TABLE students MODIFY sid INT;

说明：上面设置以及删除都给出了 CHANGE 和 MODIFY 两种，有什么区别呢？

其实细心的朋友也可以看出来， CHANGE 后要多一个列名 sid(可以修改) ，所以总结如下：

只修改类型用 MODIFY

既修改列名，也修改类型用 CHANGE

(2) 非空约束

非空约束很好理解，就是指定非空约束列的值不能为空，我们使用 NOT NULL 来实现这个功能

CREATE TABLE students (

sid INT(8) PRIMARY KEY AUTO_INCREMENT,

sname varchar(5) NOT NULL, -- sname 不为空

department varchar(32),

birthday date

);

很简单吧，我们已经将 sname 这个字段(列)在创建时添加了非空约束，如果 sname 在插入时为NULL ，则会报错 Column 'sname' cannot be null

如果表已经创建好了怎么办呢？

创建表完后，添加非空约束

ALTER TABLE students MODIFY sname VARCHAR(5) NOT NULL;

删除 sname 的非空约束

ALTER TABLE students MODIFY sname VARCHAR(5);

(3) 唯一约束

唯一约束，就是指定这个字段(列)的值必须是唯一的，这种感觉就类似主键，例如我们下面要求创建表的时候，指定 sname 不能重名

CREATE TABLE students (

sid INT(8) PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(5) NOT NULL UNIQUE, -- sname唯一

department VARCHAR(32),

birthday date

);

如果添加两条重名的记录，就会报错

INSERT INTO students VALUES (NULL,'张三','计算机系','2020-06-16');

INSERT INTO students VALUES (NULL,'张三','工商管理系','2019-06-16');

错误信息：Duplicate entry '张三' for key 'sname'

同样，如果已经创建表后，又该怎么设置或者删除唯一约束呢？

在创建表后，添加唯一约束

ALTER TABLE students MODIFY sname VARCHAR(8) UNIQUE;

删除唯一约束(本质上就是删除索引)

ALTER TABLE students DROP INDEX sname;

-- 这两种方法都是可以的

drop index sname on students;

(4) 外键约束

A：概念理解

外键的理论定义是比较复杂的，我在以前公众号写过的一篇数据库理论文章中有提及过，但是这一篇我们重点讲解 MySQL 的使用，所以，我们把理论都换成例子和通俗的大白话，先来看个问题：

学生实体和课程实体分别用关系“学生”和“课程”来表示，它们之间的联系用关系“选课”来表示

学生(学号，姓名，所在系，生日)

课程(课程编号，课程名，授课老师)

选课(学号，课程编号，成绩)

问题：判断各关系的候选键、主键、外键

答：

学生中(students) 学号可以确认唯一的学生是候选键，可做主键，姓名需要在不重名的情况下也可以，但是实际情况不能保证没有重名不合适，课程中(course) 课程编号可以确认唯一的课程是候选键，可做主键，而选课中(sc_relation)，需要由学号和课程编号共同才能确定唯一的值，所以两者共同构成候选键，并做主键

选课关系中的 学号(sc_relation.sid) 和 课程号(sc_relation.cid) ，分别代表选课关系的外键，他们分别对应 学生关系的学号(students.sid) 和 课程关系的课程号(course.sid)(不一定要同名，但是为了好理解，一般写成同名)

模拟了几张简单的表，给大家直观的理解

说明：第一张为 学生表 students ，第二张为 课程表 course，第三张为 选课表 sc_relation

看完这个例子，是不是从理解上感觉清晰了很多，那么接下来，我们就实际操作一下：

C：基本格式

CREATE TABLE 表名(

....

CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)

);

-- 创建表之后，删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

B：具体操作

我们下面，就按照这张图的规划来做

创建学生表 students，学号 sid 为主键

CREATE TABLE students (

sid INT(8) PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(5) NOT NULL UNIQUE,

department VARCHAR(32),

birthday date

);

创建课程表 course，课程号 cid 为主键

CREATE TABLE course (

cid INT(8) PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(5),

teacher VARCHAR(32)

);

创建选课关系表，sc_sid、sc_cid 分别为外键，指向学生表中的学号 sid 和 课程表中的课程号 cid

CREATE TABLE sc_relation (

sid INT(8),

cid INT(8),

cscore VARCHAR(5),

CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid),

CONSTRAINT sc_cid FOREIGN KEY (cid) REFERENCES course(cid)

);

随便提供一些数据，方便大家测试

-- 插入学生数据

INSERT INTO students VALUES (1001, '王五', '工商管理系', '2020-06-16');

INSERT INTO students VALUES (1002, '汤姆', '音乐与舞蹈系', '2020-06-16');

INSERT INTO students VALUES (1003, '杰克', '美术系', '2020-06-16');

-- 插入课程数据

INSERT INTO course VALUES (1, '大学英语', '老师1');

INSERT INTO course VALUES (2, '大学物理', '老师2');

INSERT INTO course VALUES (3, '数据库', '老师3');

INSERT INTO course VALUES (4, '操作系统', '老师4');

INSERT INTO course VALUES (5, '高等数学', '老师5');

-- 插入选课数据

INSERT INTO sc_relation VALUES (1001, 2, '88');

INSERT INTO sc_relation VALUES (1001, 3, '92');

INSERT INTO sc_relation VALUES (1001, 4, '78');

INSERT INTO sc_relation VALUES (1001, 5, '83');

INSERT INTO sc_relation VALUES (1002, 1, '77');

INSERT INTO sc_relation VALUES (1002, 2, '90');

INSERT INTO sc_relation VALUES (1002, 5, '89');

INSERT INTO sc_relation VALUES (1003, 1, '86');

INSERT INTO sc_relation VALUES (1003, 6, '88');

INSERT INTO sc_relation VALUES (1003, 6, '82');

有什么用呢？这个时候学生表以及课程表，就同选课表之间形成了关系，可视化软件编辑插入的时候，就会默认的给出一些可插入的选择，这是软件基于你设置的外键关系而自动寻找的

创建表后又怎么操作呢？

创建表之后，删除外键

ALTER TABLE sc_relation DROP FOREIGN KEY sc_sid;

创建表之后，添加外键

ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid)

C：级联操作

如果在上述选课表中已经存储着 关于学号为 1001 学生的相关选课信息，如果这个时候，在学生表中修改或者删除这条记录，就会直接报错

Cannot add or update a child row: a foreign key constraint fails (`mysql_grammar_test`.`sc_relation`, CONSTRAINT `sc_sid` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`))

所以我们使用级联操作就可以达到同时更新或者删除多张表内的相关数据

先给出基本格式：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

A：级联更新：ON UPDATE CASCADE

B：级联删除：ON DELETE CASCADE

例如测试一下

ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid) ON UPDATE CASCADE ON DELETE CASCADE;

之前不能操作的内容，现在已经可以了，例如我们在学生表中将 1001学号修改为 1008 ，这样选课表中相关的内容就会自动根据修改变化了哈

(三) 结尾

如果文章中有什么不足，欢迎大家留言交流，感谢朋友们的支持！

如果能帮到你的话，那就来关注我吧！如果您更喜欢微信文章的阅读方式，可以关注我的公众号

在这里的我们素不相识，却都在为了自己的梦而努力 ❤

一个坚持推送原创开发技术文章的公众号：理想二旬不止