0. 约束分类
列级约束：将约束声明在字段后面
表级约束：表中所有字段声明完后，在所有字段的后面声明的约束
单列约束：
多列约束：
#0.1 查询约束、表结构、索引的语句
SELECT * FROM information_schema.table_constraints WHERE table_name = 'EMPLOYEE3';
DESC EMPLOYEE3;
SHOW INDEX FROM 表名称；
#0.2 定义
为什么需要约束：数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:
- 实体完整性(Entity Integrity):例如，同一个表中，不能存在两条完全相同无法区分的记录
- 域完整性(Domain Integrity):例如:年龄范围0-120，性别范围“男/女”
- 引用完整性(Referential Integrity):例如:员工所在部门，在部门表中要能找到这个部门
- 用户自定义完整性(User-defined Integrity):例如:用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
1. NOT NULL（非空约束）
2. UNIQUE（唯一约束）
#2.1 用来限制某字段或或某列不能重复
可以列级和表级2种声明方法
#2.2 特点
同一个表可以有多个唯一约束
唯一约束可以是某一个列的值唯一，也可以是多个列组合的唯一
唯一性约束允许列值为空（NULL）,而且可以多次添加NULL值
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
MySQL会为唯一约束的列上默认创建一个唯一索引
#2.3 创建表的时候添加UNIQUE
CREATE TABLE employee3(
id INT UNIQUE,
email VARCHAR(15),
name VARCHAR(10),
salary DECIAML(10, 2),
#表约束
CONSTRAINT uni_employee3_email UNIQUE(email)
);
或者
CREATE TABLE employee3(
id INT UNIQUE,
email VARCHAR(15),
name VARCHAR(10),
salary DECIAML(10, 2),
#表约束
UNIQUE KEY uk_employee3_email(email)
);
#2.4 通过alter table table add(modify) 来实现添加UNIQUE
#类似表级约束
ALTER TABLE employee3 ADD CONSTRAINT uni_employee3_salary UNIQUE(salary);
#类型列级约束
ALTER TABLE employee3 MODIFY name VARCHAR(15) UNIQUE;
#2.5 复合的唯一约束约束(多列约束)
CREATE TABLE factory(
id int,
email varchar (15),
address varchar(18),
#表约束
CONSTRAINT uni_factory_name_password UNIQUE(email,address)
);
#2.6 删除唯一约束（和索引绑定了，通过删除索引的方式，来删除唯一约束）
--添加唯一性约束的列上会自动创建唯一索引；
--删除唯一约束只能通过删除唯一索引的方式删除；
--删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样；
--如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那就默认第一个列名即可；
#查询表里的索引
SHOW INDEX FROM factory ;
#删除表里的索引
ALTER TABLE factory DROP INDEX uni_factory_name_password;
3. PRIMARY KEY （主键约束）
#一个表必须有主键约束，用于唯一的标识表示表中的一条记录
#主键约束相当于：唯一约束 + 非空约束的组合，主键约束列不允许重复，不允许出现空值
#一个表中只能有一个主键约束，可以在列级别创建，也可以在表级别创建
#主键约束对应表中的一列或者多列（复合主键）
#如果多列组合的复合主键约束，这些列不允许有空值，并且组合的值不能重复
#主键的名总是PRIMARY,就算自己命名了也没有用
#创建主键约束时，会自动给列或者组合列上建立对应的主键索引，如果删除主键约束，对应的主键索引也会一并被删除
#不要修改主键值，因为主键是数据记录的唯一标识，如果修改了主键值，有可能会破坏数据的完整性
#3.1 创建表的同时创建主键约束
CREATE TABLE employee4(
id int PRIMARY KEY, #列级
name varchar (10),
email varchar (10),
salary decimal(10, 2)
);
CREATE table employee5(
id int,
name varchar (10),
salary decimal (10, 2),
#表级
PRIMARY KEY(id)
);
CREATE TABLE employee6(
id int,
name varchar (10),
idcard varchar (10),
PRIMARY KEY (name, idcard)
);
#3.2 通过alter table table add 来实现添加 PRIMARY
ALTER TABLE employee7 ADD PRIMARY KEY (id);
#3.3 删除主键约束（实际开发中不可能删除主键约束）
ALTER TABLE employee6 DROP PRIMARY KEY;
4. AUTO_INCREMENT（自增列）
#一般在主键上使用
#一个表最多只能有一个自增长列
#当需要产生唯一标识符或顺序值时，可设置自增长
#自增长列约束的列必须是键列（主键列、唯一键列）
#自增约束的列的数据类型必须是整数类型
#如果自定义指定了0或者null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，则直接赋值具体值
#4.1 创建表的时候添加自增列
CREATE TABLE employee8(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (10)
);
#4.2 修改表的时候添加自增列
ALTER TABLE employee8 MODIFY id int AUTO_INCREMENT;
#4.3 删除自增列
ALTER TABLE employee8 MODIFY id int;
#4.4 MySQL8.0 新特性-自增变量的持久性
举例：
1
2
3
4
删除4后，即便重启服务
后续添加数据就直接是5
因为MySQL里有日志，可以重读日志
1
2
3
5
MySQL5.7的重启服务后，
结果是：
1
2
3
4
5. FOREIGN KEY...REFERENCES... (外键约束)
#限定某个表的某个字段的引用完整性
#当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束(8)(根据外键查询效率很高)名。
#删除外键约束后，必须手动删除对应的索引
#外键约束不能夸引擎使用
#5.1 创建表的同时，建立外键约束
#@先创建主表
CREATE TABLE deptl(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
#@在创建从表
CREATE TABLE empl(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_empl_dept_id FOREIGN KEY (department_id) REFERENCES deptl(dept_id)
);
# 5.2 修改表的时候，建立外键约束
ALTER TABLE emp1 ADD CONSTRAINT fk_empl_dept_id FOREIGN KEY (department_id) REFERENCES deptl(dept_id);
#5.3 约束等级（外键约束作用下，修改数据，同步修改另外表数据的作用）
# Cascade方式:在父表上update/delete记录时，同步update/delete掉子表的匹配记录
# Set nu11方式:在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
# No action方式：如果子表中有匹配的记录，则不允许对父表对应字段进行update/delete操作
# Restrict方式：同no action，都是立即检查外键约束
# Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式
对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT 的方式
举例：
#情况1 ON UPDATE CASCADE ON DELETE SET NULL
CREATE TABLE deptl(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
CREATE TABLE empl(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_empl_dept_id FOREIGN KEY (department_id) REFERENCES deptl(dept_id) ON UPDATE CASCADE ON DELETE SET NULL
);
#5.4 删除外键约束
#第一步 查看约束名并删除约束
SELECT * FROM information_schema.table_constraints WHERE table_name = 'EMPLOYEE3';
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名；
#第二部 查看索引名并删除索引
SHOW INDEX FROM 表名称；
ALTER TABLE 从表名 DROP INDEX 索引名（=外键约束名）；
#5.5 开发场景（外键约束怎么用）
建议不使用外键，外键的功能应该在应用层代码里解决之。因为外键会消耗更多的资源，因为外键约束的系统开销而变的非常慢。
6. CHECK 检查约束
#作用：检查某个字段的值是否符合**要去，一般指的是指的范围
#5.7 版本，CHECK约束不起作用，如果定义了，但也不会报错误的
#6.1 创建表的时候建立CHECK约束
CREATE TABLE emp3(
id int,
name varchar (10),
salary decimal (10,2) CHECK(salary > 5000)
);
7. DEFAULT 默认值约束
#作用：给某个字段/列指定默认值，如果显示不赋值的时候，用默认值；如果显示赋值了，就用赋值数据
#一般使用：not null default '' 或者 not null default 0 最好表数据中不要出现null,因为null效率不高，影响提高索引效果
#7.1 创建表的时候建议DEFAULT约束
CREATE TABLE emp4(
id int,
name varchar (10),
salary decimal (10, 2) DEFAULT 2000
);
#7.2 修改表的时候修改DEFAULT约束
ALTER TABLE emp4 MODIFY salary decimal(8, 2) DEFAULT 1500;
#7.3 修改表的时候删除DEFAULT约束
ALTER TABLE emp4 MODIFY salary decimal(8, 2);
浙公网安备 33010602011771号