Mysql基础篇（5）—— 约束

为了保证数据的完整性，防止数据库中存在不符合语义的数据存在，是表级的强制规定。

约束的分类

• 根据约束数据列的限制，可分为：
  • 单列约束：每个约束只约束一列
  • 多列约束：每个约束可以约束多列数据
• 根据约束的作用范围，可分为：
  • 列级约束：只能作用在一个列上，跟在列的定义后面。
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义。
• 根据约束的作用，可分为：
  • NOT NULL 非空约束
  • UNIQUE 唯一性约束，可为null
  • PRIMARY KEY 主键约束，非空且唯一，且一个表只能有一个
  • FOREIGN KEY 外键约束
  • CHECK 检查约束
  • DEFAULT 默认值约束
  • AUTO_INCREAMENT 自增，一张表最多只能有一个

相关语法

查看某个表已有的约束

SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

添加约束

建表时

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

建表后

alter table modify 字段名 数据类型 约束名;
alter table add unique key(字段列表);

删除约束

普通

alter table t1 modify 字段名 数据类型; 	//相当于修改字段定义，不设置相关约束

删除唯一键约束

添加唯一性约束的列上会自动创建唯一索引，删除唯一键约束只能通过删除唯一索引。

alter table 
drop index 索引名

删除主键约束

alter table t1 drop primary key;

外键

添加外键

create table  t1(
id int primary key,
dept_id int not null,
name varchar(50),
constraint fk_name foreign key(dept_id) references dept(id) on update cascade on delete set null
);

on update cascade on delete set null这部分是设置约束等级

外键约束的约束等级

• Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作

• Restrict方式 ：同no action， 都是立即检查外键约束（默认）

• Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

#####　删除外键约束

1、第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
alter table 从表名 drop foreign key 外键约束名;
2、查看索引名和删除索引
show index from 从表名;
alter table 从表名 drop index 索引名;

ps:

• 外键约束不适合高并发
• 外键约束不允许跨引擎存储

CHECK约束

mysql5.7有但是没用，mysql8.0有用

添加check约束

create table t1(
id int ,
name varchar(40),
sex varchar(1) check('男', '女'),
age int check(age > 0)
);

QA

为什么建表时，加 not null 、default ''或 default 0?

1.不好比较，null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。

2.效率不高影响索引效果。

MySQL8.0新特性自增变量的持久化

mysql8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，8.0之后不会。8.0之前对于自增主键的分配规则，是由InnoDB数据字典内部一个 计数器来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。8.0之后这个计数器会持久化到重做日志中，mysql重启后会读取重做日志来初始化计数器的值。