Mysql学习笔记之约束
1.简介
MySQL中的约束用于定义表中数据的规则，以确保数据的准确性和完整性。以保证数据表中数据的有效性、正确性和完整性。
2.约束分类
在mysql中约束分类如下：
| 约束 | 说明 | 关键字 |
|---|---|---|
| 主键约束 | 每个表只能有一个主键；主键的值必须唯一，不能为NULL；主键用于唯一标识表中的每一行记录。 | PRIMARY KEY |
| 唯一约束 | 保证列中的所有值都是唯一的；可以有多个唯一约束；唯一约束列可以包含NULL值。 | UNIQUE |
| 非空约束 | 确保列中的值不能为NULL；可以有多个非空约束。 | NOT NULL |
| 默认约束 | 当没有为列指定值时，将使用默认值。 | DEFAULT |
| 检查约束 | 保证字段值满足某一个条件（早期版本不支持）。 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性；外键包含的列必须在另外一张表中作为主键或者唯一键存在。 | FOREIGN KEY |
3.实例
在前面章节建立student表时，并没有进行约束限制，建表语句如下：
CREATE TABLE `students` (
`id` int COMMENT '学号',
`name` varchar(255) COMMENT '姓名',
`sex` varchar(2) COMMENT '性别',
`age` int COMMENT '年龄'
)COMMENT '学生表';
在学生表中，每个学生的学号应该是唯一且不为空的，因此可以将id设为主键，而每个学生应该都有名字，因此name必须是非空的，但名字可以重复，所以不能设置为唯一，性别除了‘男’就是‘女’，可以设置检查约束来确保字段的正确性，同理年龄也应该是非空限制。
加上约束后的建表语句如下：
CREATE TABLE `students` (
`id` int PRIMARY KEY COMMENT '学号' ,
`name` varchar(255) NOT NULL COMMENT '姓名',
`sex` varchar(2) CHECK(sex IN('男','女')) COMMENT '性别',
`age` int NOT NULL COMMENT '年龄'
) COMMENT '学生表';
接下来尝试插入一些错误的数据，看约束是否生效
- 验证主键
前面提到主键是不能为空，且唯一的，因此可以从两方面验证主键是否生效
-- 主键留空
INSERT INTO students (id,`name`,sex,age) VALUES(NULL,"张三","男",20);
提示id不能为空
-- 插入相同的主键
INSERT INTO students (id,`name`,sex,age) VALUES(1,"张三","男",20);
INSERT INTO students (id,`name`,sex,age) VALUES(1,"李四","男",20);
提示主键重复
- 验证非空
将名称或者年龄任意字段留空
-- 名称留空
INSERT INTO students (id,`name`,sex,age) VALUES(2,NULL,"男",20);
注意
NULL和空字符串不等 NOT NULL 不能限制空字符串，如果需要限制空字符串需要使用检查约束（CHECK)进行限制
- 验证检查约束
对性别字段插入除（男，女）外的别的字符
INSERT INTO students (id,`name`,sex,age) VALUES(2,"李四","你",20);
检查不通过
4.外键
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
语法：
- 添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) 删除/更新行为
);
-- 或者
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) 删除/更新行为;
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
4.1 删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。主要有以下几种：
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION一致) 默认行为 |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持) |
4.2 实例
先准备两张表
1.班级表（class)
2.学生表（students）
- NO ACTION（RESTRICT）
首先将学生表中classid设置为外键，管理class表的id字段（使用默认删除/更新行为）
ALTER TABLE students ADD CONSTRAINT fk_classIs FOREIGN KEY (classid) REFERENCES class (id);
删除id为1的班级,提示错误，存在外键引用不能删除
2. CASCADE
ALTER TABLE students ADD CONSTRAINT fk_classIs FOREIGN KEY (classid) REFERENCES class (id) on update cascade on delete cascade ;
原始数据
将id为3的班级设置为id为4
3. SET NULL
ALTER TABLE students ADD CONSTRAINT fk_classIs FOREIGN KEY (classid) REFERENCES class (id) on update SET NULL on delete SET NULL;
删除id为4的班级信息
DELETE FROM class WHERE id = 4;
4.3 拓展
从上述删除/更新行为可以看出，外键的存在会导致数据表在插入、删除、更新数据时，都会先验证该记录是否存在外键，因此会带来额外的操作从而影响性能。所以外键的使用需要根据实际场景慎重考虑。