MYSQL-约束篇

1.概念:约束是作用在表中字段上的规则，用于限制储存在表中的数据

2.目的：保证数据库中数据的正确、有效和完整性

3.分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证字段所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一个条件{8.0.16之后}	CHECK
外键约束	用来让两张表之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

 外键约束：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否存在对应外键，如果有则设置子表中该外键值为null。 (要求该外键允许取null)
SET DEFAULT	父表有变更时，子表将外键设置成一个默认值(Innodb不支持)

 设置外键约束案例：

#子表
CREATE TABLE userInfo(
	id int PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
   name varchar(10) NOT null ,
   age int CHECK ( age > 0 && age <= 120 ) COMMENT '年龄',
   status char(1) DEFAULT '1' COMMENT '状态',
   gender char(1) COMMENT '性别',
   dept_id int COMMENT '部门id'
 ) COMMENT '用户表';

#父表
CREATE TABLE dept(
	id int PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
   name varchar(20) NOT null COMMENT '部门名称'
) COMMENT '部门表';

#添加数据

INSERT INTO dept(name) VALUES ('销售部'),('研发部'),('宣传部')

INSERT INTO userinfo(name,gender,dept_id) VALUES ('狸花','男',1),('吾心悦','女',1),('伍月','女',1);

#添加外键
ALTER TABLE userinfo ADD CONSTRAINT user_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

查看表约束信息：

 

删除主表数据效果：

设置外键约束删除行为：

此处展示设置行为为SET NULL:

#约束名称存在先删除
ALTER TABLE `userinfo` DROP FOREIGN KEY `user_dept_id`;  
  
#然后重新添加外键约束，包括设置想要的行为  
ALTER TABLE `userinfo`  
ADD CONSTRAINT `user_dept_id` FOREIGN KEY (`dept_id`)  
REFERENCES `dept`(`id`)  
ON DELETE SET NULL;

效果：

删除主表id为1的部门对应子表dept_id为1的字段值变为了null

外键约束相关语法：

#添加外键约束方法1
CREATE TABLE `userinfo` (
 字段名 数据类型
 ....
 CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

#添加外键约束方法2
ALTER TABLE 子表名 ADD CONSTRAINT 外键名 FOREIGN KEY (子表外键字段名) REFERENCES 主表(主表列名);

#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

注意：

MyISAM 存储引擎不支持外键约束。如果你需要使用外键约束，你应该考虑使用 InnoDB 存储引擎。你可以通过将 ENGINE=MyISAM 改为 ENGINE=InnoDB 来更改存储引擎。

修改表的存储引擎：

ALTER TABLE 表名 ENGINE = InnoDB;