概述

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
        实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
        域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
        引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
        用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门
经理的工资不得高于本部门职工的平均工资的5倍。

约束分类

角度1：约束的字段的个数

单列约束 vs 多列约束

角度2：约束的作用范围

列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束

角度3：约束的作用（或功能）

① not null (非空约束)

② unique  (唯一性约束)

③ primary key (主键约束)

④ foreign key (外键约束)

⑤ check (检查约束)

⑥ default (默认值约束)

非空约束[NOT NULL]

限定某个字段/某列的值不允许为空

CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

在CREATE TABLE时添加

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)

);

DESC test1;

INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

#错误：Column 'last_name' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(2,NULL,'tom1@126.com',3400);

#错误：Column 'id' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(NULL,'Jerry','jerry@126.com',3400);

INSERT INTO test1(id,email)
VALUES(2,'abc@126.com');

UPDATE test1
SET last_name = NULL
WHERE id = 1;

UPDATE test1
SET email = 'tom@126.com'
WHERE id = 1;

在ALTER TABLE时添加[MODIFY]

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

在ALTER TABLE时删除[MODIFY]

ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

唯一性约束[unique]

用来限制某个字段/某列的值不能重复

列级约束

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);

表级约束

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

在CREATE TABLE时添加

在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

可以向声明为unique的字段上添加null值。而且可以多次添加null

CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR(15) ,
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);

DESC test2;


SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test2';

#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',4500);

#错误：Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom1','tom1@126.com',4600);

#错误：Duplicate entry 'tom@126.com' for key 'test2.uk_test2_email'
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1','tom@126.com',4600);

#可以向声明为unique的字段上添加null值。而且可以多次添加null
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);

INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);

SELECT * FROM test2;

在ALTER TABLE时添加[ADD/MODIFY]

#方式1：
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#方式2：
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

复合的唯一性约束

多个字段的组合是唯一的

CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),

#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);

INSERT INTO USER
VALUES(1,'Tom','abc');
#可以成功的：
INSERT INTO USER
VALUES(1,'Tom1','abc');

SELECT *
FROM USER;

删除唯一性约束

添加唯一性约束的列上也会自动创建唯一索引。

删除唯一约束只能通过删除唯一索引的方式删除。

删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test2';

DESC test2;

#如何删除唯一性索引
ALTER TABLE test2
DROP INDEX last_name;

ALTER TABLE test2
DROP INDEX uk_test2_sal;

主键约束[PRIMARY KEY]

用来唯一标识表中的一行记录，实现实体完整性，保障每条记录区别于其他记录

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询
的，就根据主键查询，效率更高）。

在CREATE TABLE时添加

一个表中最多只能有一个主键约束。

MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用

#错误：Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

# 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录。
CREATE TABLE test4(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
CREATE TABLE test5(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id)  #没有必要起名字。
);

SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test5';

INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');

#错误：Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');

#错误：Column 'id' cannot be null
INSERT INTO test4(id,last_name,salary,email)
VALUES(NULL,'Tom',4500,'tom@126.com');

SELECT * FROM test4;

复合约束

如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),

PRIMARY KEY (NAME,PASSWORD)

);
#如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
INSERT INTO user1
VALUES(1,'Tom','abc');

INSERT INTO user1
VALUES(1,'Tom1','abc');
#错误：Column 'name' cannot be null
INSERT INTO user1
VALUES(1,NULL,'abc');

SELECT * FROM user1;

在ALTER TABLE时添加[ADD]

CREATE TABLE test6(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

DESC test6;

ALTER TABLE test6
ADD PRIMARY KEY (id);

删除主键约束

在实际开发中，不会去删除表中的主键约束！

ALTER TABLE test6
DROP PRIMARY KEY;

自增长列[AUTO_INCREMENT]

当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15) 
);
#开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键
#对应的字段去赋值了。
INSERT INTO test7(last_name)
VALUES('Tom');

SELECT * FROM test7;

#当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值
INSERT INTO test7(id,last_name)
VALUES(0,'Tom');

INSERT INTO test7(id,last_name)
VALUES(NULL,'Tom');

INSERT INTO test7(id,last_name)
VALUES(10,'Tom');

INSERT INTO test7(id,last_name)
VALUES(-10,'Tom');

在ALTER TABLE 时添加[MODIFY]

CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(15) 
);

DESC test8;

ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;

在ALTER TABLE 时删除[MODIFY]

ALTER TABLE test8
MODIFY id INT ;

新特性:自增变量的持久化

MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

#在MySQL 8.0中演示
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test9
VALUES(0),(0),(0),(0);

SELECT * FROM test9;

DELETE FROM test9
WHERE id = 4;

INSERT INTO test9
VALUES(0);

DELETE FROM test9
WHERE id = 5;

#重启服务器

SELECT * FROM test9;

INSERT INTO test9
VALUES(0);

外键约束[FOREIGN KEY]

限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表

在CREATE TABLE 时添加

#①先创建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);

#②再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)

);

#上述操作报错，因为主表中的dept_id上没有主键约束或唯一性约束。
#③ 添加
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);

DESC dept1;

#④ 再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)

);

DESC emp1;


SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'emp1';

外键的效果

#添加失败
INSERT INTO emp1
VALUES(1001,'Tom',10);

#
INSERT INTO dept1
VALUES(10,'IT');
#在主表dept1中添加了10号部门以后，我们就可以在从表中添加10号部门的员工
INSERT INTO emp1
VALUES(1001,'Tom',10);

#删除失败
DELETE FROM dept1
WHERE dept_id = 10;

#更新失败
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;

在ALTER TABLE时添加外键[ADD]

CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'emp2';

约束等级

Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null
No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action， 都是立即检查外键约束
Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置
成一个默认的值，但Innodb不能识别

# on update cascade on delete set null
CREATE TABLE dept(
    did INT PRIMARY KEY,		#部门编号
    dname VARCHAR(50)			#部门名称
);

CREATE TABLE emp(
    eid INT PRIMARY KEY,  #员工编号
    ename VARCHAR(5),     #员工姓名
    deptid INT,		  #员工所在的部门
    FOREIGN KEY (deptid) REFERENCES dept(did)  ON UPDATE CASCADE ON DELETE SET NULL
    #把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

INSERT INTO dept VALUES(1001,'教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');


INSERT INTO emp VALUES(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);


UPDATE dept
SET did = 1004
WHERE did = 1002;

DELETE FROM dept
WHERE did = 1004;


SELECT * FROM dept;

SELECT * FROM emp;

结论：对于外键约束，最好是采用: `ON UPDATE CASCADE ON DELETE RESTRICT`
[更新的时候同步更新，删除时谨慎考虑]的方式。

删除外键约束[DROP FOREIGN KEY/DROP INDEX]

#删除外键约束

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

#再手动的删除外键约束对应的普通索引
SHOW INDEX FROM emp1;

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;

开发经验

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。因为外键约束的系统开销而变得非常慢，在应用层面完成检查数据一致性的逻辑

不得使用外键与级联，一切外键概念必须在应用层解决。

说明：
（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。
如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群；
级联更新是强阻塞，存在数据库更新风暴的风险；
外键影响数据库的插入速度 。

检查约束[CHECK]

检查某个字段的值是否符号xx要求，一般指的是值的范围

# MySQL5.7 不支持CHECK约束，MySQL8.0支持CHECK约束。

CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);

INSERT INTO test10
VALUES(1,'Tom',2500);

#添加失败
INSERT INTO test10
VALUES(2,'Tom1',1500);

SELECT * FROM test10;

默认值约束[DEFAULT]

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

在CREATE TABLE添加

CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);

DESC test11;

INSERT INTO test11(id,last_name,salary)
VALUES(1,'Tom',3000);

INSERT INTO test11(id,last_name)
VALUES(2,'Tom1');

SELECT * 
FROM test11;

在ALTER TABLE添加[MODIFY]

CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);

DESC test12;

ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;

在ALTER TABLE删除[MODIFY]

ALTER TABLE test12
MODIFY salary DECIMAL(8,2);


SHOW CREATE TABLE test12;