MySQL约束

约束（constraint）

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

• 为了保证数据的完整性，sql规范以约束的方式对表数据进行额外的条件限制

• 约束是表级的强制规定

查询表的约束

-- 查询表的约束
-- information_schema 系统库
-- table_constraints表名称，专门存储各个表的约束
select * from information_schema.table_constraints
where table_name = 'employees'

约束的分类

• 角度一：约束的字段个数

  单列约束和多列约束

• 角度二：

  列级约束：将此约束声明在对应字段的后面

  表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束

• 角度三：约束的作用（功能）

1、not null (非空约束)

• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
• 一个表可以有很多列都分别限定非空
• 空字符串'' 不等于null，0也不等于null

-- 创建时声明
create table test_null(
	id int,
	`name` varchar(20) not null,
	`email` varchar(25),
	salary decimal(10,2)
)

-- 创建好后修改约束
alter table test_null
modify `name` varchar(20) not null

2、unique(唯一性约束)

• 同一个表可以有多个唯一约束
• 唯一约束可以是某一个列的值唯一
• 也可以多个列组合的值唯一
• 唯一约束允许列值为空或者null
• 声明unique的字段可以多次添加null值
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
• MySQL会给唯一约束的列上默认创建一个唯一索引

-- 创建表时添加
create table test_unique1(
	id int unique,
	`name` varchar(20),
	email varchar(25) unique,
	salary decimal(20,2)
)

-- 也可以在声明字段后添加约束
create table test_unique2(
	id int unique,
	`name` varchar(20),
	email varchar(25),
	salary decimal(20,2),
	constraint uk_unique2_email unique(email)
)

复合的唯一约束：

把多个字段合在一起作为一个唯一约束

-- 复合的唯一性约束
create table test_unique3(
	id int,
	`name` varchar(20),
	`password` varchar(20),
	-- 表级约束
	constraint ut_test_unique3_name_password unique(`name`,`password`)
)
insert into test_unique3 values
(1,'Tom','123')   -- 插入成功

insert into test_unique3 values
(1,'Tom','1234')   -- 插入成功


insert into test_unique3 values
(1,null,null),(1,null,null)   -- 插入成功

删除唯一性索引：

• 添加唯一性约束的列上也会自动创建唯一索引
• 删除唯一约束只能通过删除唯一索引的方式删除
• 删除时需要制定唯一索引名，唯一索引名就和唯一约束名一样
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和（）中排列在第一个的列名相同。也可以自定义唯一约束名。

-- 删除唯一性约束
alter table test_unique3
drop index ut_test_unique3_name_password

3、primary key(主键约束)

用来唯一的标识表中的记录。

• 相当于唯一约束和非空约束的组合。
• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
• 主键约束对应这表中的一列或者多列（复合主键约束）
• 如果是多列组合的复合主键约束，那么这些列都不允许空值，并且组合的值不允许重复
• MySQL的主键名总是primary，就算自己命名了主键约束也没用
• 当创建主键约束时，系统默认会在所在的列或组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
• 需要注意的一点是，不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。
• 主键可以存入负值

-- 在创建表示添加主键约束，列级约束
create table test_primary(
	id int primary key,
	`name` varchar(15),
	`password` varchar(25)
)

-- 在创建表示添加主键约束，表级约束
create table test_primary2(
	id int,
	`name` varchar(15),
	`password` varchar(25),
	constraint uk_primary_id primary key(id)
);

复合的主键约束

-- 复合的主键约束
create table test_primary3(
	id int,
	`name` varchar(15),
	`password` varchar(25),
	primary key (`id`,`name`)
)

删除主键约束。实际开发中不会去删除主键约束

alter table test_primary3
drop primary key

自增列：

auto_increment

某个字段的值自增

• 一个表最多只能有一个自增长列
• 当需要战胜唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了0和null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。
• AUTO_INCREMENT数据列必须具备NOT NULL属性。
• 在开发过程中如果主键作用的字段上声明有auto_increment，则我们在添加数据时，就不要给主键对应的字段去赋值

MySQL8.0新特性：

先说5.7版本。当主键设置自增列后，添加信息可以不显式添加主键，那么主键会自增。如：

create table test_auto(
	id int primary key auto_increment
)
insert into test_auto
values (0),(0),(null),(null)
-- 这时表中有1、2、3、4四个数据

但是当删除id = 4的列后，在此添加0，表中数据为 1、2、3、5。也就是造成了裂缝，因为MySQL5.7版本的自增索引保存在了系统内存中。当服务器重启后，会把索引重置为id最大数

但是在MySQL8.0中，索引的值被持久化在重做日志中，即关闭服务器也不会重置索引。

4、foreign key(外键约束)

限定某个表的某个字段的引用完整性

• 主表（父表）：被引用的表
• 从表（子表）：引用别人的表

特点

• 从表的外键列必须引用、参考主表的主键或者唯一约束的列。因为被依赖、参考的值必须是唯一的
• 在创建外键约束时，如果不给外键约束命名，**默认名不是列名，而是自动产生一个外键名，**也可以指定外键约束名。
• 创建表时就指定外键约束的话，先创建主表，再创建从表
• 删表时，先删从表，再删主表
• 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
• 在从表中指定外键约束，并且一个表可以建多个外键约束
• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误。
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。（根据外键查询效率很高）
• 删除外键后，必须手动删除对应的索引

在创建表时添加外键约束

-- 主表
create table dept(
	dept_id int primary key auto_increment,
    dept_name varchar(25)
)
-- 从表
create table emp(
	emp_id int primary key auto_increment,
	emp_name varchar(25),
	dept_id int,
	constraint fk_emp_dept_id foreign key (dept_id) references dept(dept_id)
)

如果要在从表中添加数据，那么从表中外键列的数据一定要存在于主表对应的列中。

主表中被引用的数据不能被删除，要想删除就得先删除从表中的数据。

外键约束等级

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

对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT的方式。

删除外键约束

先查看约束名和删除外键约束

select * from information_schema.table_constraints
where table_name = 'emp'

alter table emp
drop foreign key fk_emp_dept_id

然后查看索引名和删除索引

show index from emp

alter table emp
drop index fk_emp_dept_id

5、check(检查约束)

MySQL5.7不支持，MySQL8.0支持

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

create table test_check(
	id int,
	last_name varchar(25),
	salary decimal(10,2) check(salaey>2000)
)

insert into test_check
values(1,'Tom',2500) -- 成功

insert into test_check
values(2,'Jack',1500) -- 失败

6、default(默认值约束)

create table test_default(
	id int,
	last_name varchar(25) default 'Tom'
)

关于默认值：

1. 通常在建表的时候要加上 not null default '' 或者 default 0。原因是不想在表中出现 null 值
2. 不想要null值的原因：
   • 不好比较。null 值是一种特殊值，比较时只能用专门的 is null 或者 is not null 来比较。碰到运算符，通常返回 null
   • 效率不高。影响提高索引效果。因此，我们往往在建表时加上 not null default '' 或者 default 0
3. 并不是每个表都可以任意选择存储引擎？
   • 外键约束不能跨引擎使用。
   • MySQL支持多种存储引擎，每个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。