约束(constraint)
1. 数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。
它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作
或错误信息而提出的。
2. 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后
通过 ALTER TABLE 语句规定 约束。
#约束
/*
为什么需要约束 ：为了保证数据的完整性
什么叫约束：对表中字段的限制
约束的分类：1.约束的字段的个数: ①单列约束 ②多列约束
2.约束的作用范围：
列级约束：将此约束声明对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束。
3.约束的作用（功能）
① not null （非空约束）
②unique （唯一性约束）
③primary key （主键约束）
④foreign key （外键约束）
⑤check（检查约束）注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果
⑥default （默认值约束）
4.如何添加/删除约束： ALTER TABLE 时增加约束，删除约束
*/
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）查询约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
-
非空约束：
# 非空约束 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@74.com',8600);
INSERT INTO test1(id,last_name,email,salary)
VALUES (2,NULL,'tom@74.com',8600); #报错
INSERT INTO test1(id,last_name,email,salary)
VALUES (NULL,'jerry','tom@74.com',8600); #报错
INSERT INTO test1(id,email)
VALUES (2,'ab@qq.com'); #报错
UPDATE test1
SET last_name=NULL #报错，修改不了
WHERE id=1;
SELECT * FROM test1
#在ALTER TABLE 时添加约束
DESC test1;
ALTER TABLE test1 #如果email有值就能修改成功，如果为null 就不行
MODIFY email VARCHAR (25) NOT NULL;
#在ALTER TABLE 时删除约束
ALTER TABLE test1
MODIFY email VARCHAR (25) NULL;
-
唯一性约束：
关键字： UNIQUE
用来限制某个字段/某列的值不能重复。
#unique （唯一性约束）
#在CREATE TABLE 时添加约束
CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR (15),
email VARCHAR (25) ,
salary DECIMAL (10,2),
#表级约束 CONSTRAINT uk_test2_email 也可以不用，但没有约束名
CONSTRAINT uk_test2_email UNIQUE (email)
);
#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
DESC test2;
INSERT INTO test2(id,last_name,email,salary)
VALUES (1,'Tom','toml@128.com',7800);
#错误：
INSERT INTO test2(id,last_name,email,salary)
VALUES (1,'Tom1','tom@128.com',7800);
#错误：
INSERT INTO test2(id,last_name,email,salary)
VALUES (2,'Tom1','tom@128.com',7800);
#可以向声明为unique 的字段上添加null值 而且可以多次添加null
INSERT INTO test2(id,last_name,email,salary)
VALUES (2,'Tom1','NULL',7800);
SELECT * FROM test2
INSERT INTO test2(id,last_name,email,salary)
VALUES (3,'Tom2',NULL,7800);
SELECT * FROM test2
#在ALTER TABLE 时添加约束
DESC test2;
UPDATE test2
SET salary=00
WHERE id=3; #因为数据里面有两个相同的数据了，为了能够添加约束成功，把数据改成不同的
#方式1
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#方式2
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
关于复合唯一约束：
#学生表
create table student(
sid int, #学号
sname varchar(20), #姓名
tel char(11) unique key, #电话
cardid char(18) unique key #身份证号
);
#课程表
create table course(
cid int, #课程编号
cname varchar(20) #课程名称
);
#选课表
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid) #复合唯一
);
insert into student
values(1,'张三','13710011002','101223199012015623');#成功
insert into student
values(2,'李四','13710011003','101223199012015624');#成功
insert into course
values(1001,'Java'),(1002,'MySQL');#成功
select * from student;
insert into student_course
values (1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功
删除唯一约束：
#删除唯一性约束
/*
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，
那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
*/
DESC test2;
#如何删除唯一性索引
ALTER TABLE test2
DROP INDEX last_name;
-
主键( PRIMARY KEY)约束
作用：用来唯一标识表中的一行记录。
特点：主键约束相当于唯一约束+非空约束的组合，
主键约束列不允许重复，也不允许出现空值。
1. 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建
2. 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
3. MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
①建表时指定主键约束：
# PRIMARY KEY （主键约束）
#在CREATE TABLE 时添加约束
#一个表中最多只能有一个主键约束
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR (25)
);
# 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录
# MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
CREATE TABLE test4(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR (25),
CONSTRAINT pk_test5_id PRIMARY KEY(id) #没必要
);
SELECT * FROM test4
②建表后增加主键约束：
# 在ALTER TABLE 时添加约束
CREATE TABLE test6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(25)
);
DESC test6;
ALTER TABLE test6
ADD PRIMARY KEY (id);
复合主键：
# 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
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,'Toml','abc');
SELECT * FROM user1;
删除主键约束：
alter table 表名称 drop primary key;
#如何删除主键约束 (实际开发中，不会去删除表中的主键约束)
ALTER TABLE test6 DROP PRIMARY KEY;
自增列 (AUTO_INCREMENT)：
作用：某个字段的值自增
特点和要求： ① 一个表最多只能有一个自增长列。
② 自增长列约束的列必须是键列（主键列，唯一键列）。
③如果自增列指定了 0 和 null，会在当前最大值的基础上自增；
如果自增列手动指定了具体值，直接赋值为具体值。
#自增列：AUTO_INCREMENT
#在CREATE TABLE时添加
#当我们向主键（含AUTO_INCREMENT）的字段上添加0或null时，实际上会自动的往上添加指定的字段数值
#开发中，一旦主键作用的字段上声明有AUTO——INCREMENT，则我们在添加数据时，就不要给主键对应的字段赋值了
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(25)
);
INSERT INTO test7(id,last_name)
VALUES (1001,'Tom');
SELECT *FROM test7;
#在alter table 时添加
CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(25)
);
DESC test8;
ALTER TABLE test8
MODIFY id INT auto_increment;
#在alter table 时删除
ALTER TABLE test8
MODIFY id INT;
删除自增约束：
ALTER TABLE test8
MODIFY id INT auto_increment;
#在alter table 时删除
ALTER TABLE test8
MODIFY id INT;
MySQL 8.0新特性—自增变量的持久化：
1. MySQL 5.7系统的自增主键没有持久化。
2. 而MySQL 8.0将自增主键的计数器持久化到 重做日志 中。
每次计数器发生改变，都会将其写入重做日志中。
如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。
-
外键(FOREIGN KEY)约束：
关键字： FOREIGN KEY
特点： ①从表的外键列，必须引用/参考主表的主键或唯一约束的列
②在创建外键约束时，如果不给外键约束命名，默认名不是列名，
而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
③创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
④删表时，先删从表（或先删除外键约束），再删除主表
⑤从表的外键列与主表被参照的列名字可以不相同，
但是数据类型必须一样，逻辑意义一致。
(2)建表后的：
#FOREIGN KEY (外键约束)
#在CREATE TABLE 时添加
#主表和从表：父表和字表
#①先创建主表
CREATE TABLE dept1(
depat_id INT,
depat_name VARCHAR (15)
);
#②在创建从表
CREATE TABLE emps1(
emp_id INT PRIMARY KEY auto_increment,
emp_name VARCHAR (15),
department_id INT
#表的约束
CONSTRAINT fk_empi_depat_id FOREIGN KEY(department_id) REFERENCES depat1(dept_id)
);
`
#上述操作报错，因为表中的depat_id上没有主键约束或唯一性约束
#③添加
ALTER TABLE dept1
ADD PRIMARY KEY (depat_id);
#在创建从表
CREATE TABLE emps1(
emp_id INT PRIMARY KEY auto_increment,
emp_name VARCHAR (15),
department_id INT,
#表的约束
CONSTRAINT fk_empi_depat_id FOREIGN KEY(department_id) REFERENCES dept1(depat_id)
);
DESC emps1;
#外键效果
#添加失败
INSERT INTO emp1
VALUES (1001,'Tom',10);
INSERT INTO dept1
VALUES(10,'IT');
#在主表中添加了10号部门以后，我们就可以在从表中添加10号部门的员工
INSERT INTO emps1
VALUES (1001,'Tom',10) ;
#删除失败
DELETE FROM dept1
WHERE depat_id=10;
#结论：对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
约束等级：
Cascade方式 Set null方式 No action方式 Restrict方式 Set default方式
如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
阿里开发规范：
①强制 不得使用外键与级联，一切外键概念必须在应用层解决。
②说明：外键与级联更新适用于 单 机低并发 ，不适合 分布式 、 高并发集群 ；
级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响数据库的 插入速度 。
#CHECK 约束
#mysql8.0才支持的check约束
CREATE TABLE test10(
id INT,
lst_name VARCHAR (15),
salary DECIMAL (10,2) CHECK(salary>2000)
);
INSERT INTO test10
VALUES (1,'Tcm',2500);
#添加失败
INSERT INTO test10
VALUES (1,'Tcm',1500);
#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);
SELECT * FROM test11;
#在alter table 添加约束
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);
ALTER TABLE test12
MODIFY salary DECIMAL(8.2) DEFAULT 2500;
#删除
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);
