Mysql六大约束_使用六种约束的例子

Mysql六大约束（constraint）

约束的作用

在设计表的时候使用约束可以帮助维护表中记录的完整性和有效性

主键约束(PRIMARY KEY)

• 特点：非空且唯一，每个表只能有一个主键

CREATE TABLE `student`(
	`id` int PRIMARY KEY
);
1
2
3

非空约束(NOT NULL)

CREATE TABLE `student`(
	`id` int NOT NULL
);
1
2
3

唯一约束(UNIQUE)

• 列级约束用法

CREATE TABLE `student`(
	`id` int UNIQUE
);
1
2
3

• 表级约束用法

CREATE TABLE `student`(
	`id` int,
	`studentId` int,
	UNIQUE(`id`, `studentId`)
);
1
2
3
4
5

默认约束(DEFALUT)

create table `student`(
	`id` int,
	`core` int DEFALUT 1 
);
1
2
3
4

检查约束(CHECK)

Mysql在Mysql8.0.16以后的版本才提供检查约束
特点：保证字段值满足某一个/多个条件

• 列级约束用法

CREATE TABLE student(
	`id` int,
	`age` int CHECK(`age` > 0 && `age` <= 120)
);
1
2
3
4

• 表级约束用法

CREATE TABLE `user`(
	`id` int,
	`age` int,
	CHECK(`id` < 100 && `age` > 10)
)
1
2
3
4
5

外键约束

外键的作用

1. 外键可以保证数据完整性和一致性，级联操作非常方便；
2. 使用外键可以将数据完整性判断托付给数据库完成，减少程序的代码量；

外键语法

-- 建表添加外键约束语法
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
1
2
3

--修改表添加外键约束语法
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
1
2
3

-- 删除外键
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
1
2

外键的工作方式

• 阻止执行
  ① 从表插入新行，其外键值不是主表的主键值便阻止插入；
  ② 从表修改外键值，新值不是主表的主键值便阻止修改；
  ③ 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
  ④ 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

• 级联执行
  ① 主表删除行，连带从表的相关行一起删除。
  ② 主表修改主键值，连带从表相关行的外键值一起修改。

外键的工作方式 - 实例演示

CREATE TABLE `user`(
	`id` int PRIMARY KEY,
	`name` char(50)
);

CREATE TABLE `student`(
	`student_id` int PRIMARY KEY,
	`class` int,
	CONSTRAINT `fk` FOREIGN KEY (`student_id`) REFERENCES `user`(`id`)
);

INSERT INTO `user`
VALUES	(1, "张三"),
		(2, "李四"),
		(3, "王五"),
		(4, "赵六");
			 
			 
INSERT INTO `student`
VALUES	(1, 1),
		(2, 1),
		(3, 2),
		(4, 3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

@ 表1，user表

@ 表2，student表

• 此时两个表的数据已经被外键约束，执行以下操作：

 1. 操作主表中将被外键约束的数据
	UPDATE `user` SET `id` = 5 WHERE `name` = "张三";  -- SQL报错
	
 2. 操作从表，将被约束的数据修改为主表中<不存在>的外键数据 
	UPDATE `student` SET `student_id` = 5 WHERE `class` = 3;  -- SQL报错
	
 3. 操作从表，将被约束的数据修改为主表中<存在>的外键数据 
	UPDATE `student` SET `student_id` = 4 WHERE `class` = 2;  -- SQL语句通过
1
2
3
4
5
6
7
8

• SQL报错信息（序号与SQL语句相对应）

1. Cannot delete or update a parent row: a foreign key constraint fails (myDataBase.student, CONSTRAINT fk FOREIGN KEY (student_id) REFERENCES user (id))

2. Cannot add or update a child row: a foreign key constraint fails (myDataBase.student, CONSTRAINT fk FOREIGN KEY (student_id) REFERENCES user (id))

示例中的三条SQL演示了外键的工作方式中的第②、④点。

外键的缺点

• 并发问题。在使用外键的情况下，每次修改/删除表数据都需要去另外一个表检查数据，需要获得额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁；
• 扩展性问题。比如从"Mysql"将数据迁移到"Oracle"，外键依赖于数据库本身的特性，做迁移可能不方便；
• 不利于分库分表。在水平拆分和分库的情况下，外键是无法生效的。将数据库关系的维护放入应用程序中，可以为将来的分库分表省去很多的麻烦。

数据库知识补充

什么是级联操作

计算机科学中的级联指的是多个对象之间的映射关系，通过建立数据之间的级联关系可以提高管理效率

数据库水平扩容

• 水平扩容也叫平行扩容，它的具体做法是以增加节点的方式扩展整个数据库，以达到系统对数据库的需求。简而言之，就是把数据库从已有的数据转移到另一个内存大的数据库当中。
• 数据在迁移过程中需要满足业务可用和数据不可丢失两个方面。在数据迁移前一定要做好数据备份工作。

数据库垂直扩容

• “水平”是从左往右的一种方式，那么“垂直”就是一种自上而下的方式，扩容方向不同，难度也不同。
• 垂直扩容是使用增加内存的方式使得整个系统的容量扩充，扩充难度比较低，不过会受到系统内存的限制，任何系统都有相应的运行内存和存储内存，不可能无限制扩大，当扩大到一定程度的时候就会停止，就要考虑其他方式解决数据库满的问题。

欢迎评论、交流，如若文中描述错误，敬请指导。