mysql约束

约束(constraint)
保证数据完整性
可以在创建表时规定约束(creat table)，或者表创建之后通过alter table规定约束

# 查看employees表中的约束
select * from information_schema.table_constraints where table_name = 'employees';
# 查看test_binary表中的索引
show index from test_binary;

非空约束

# 字段的值不能为null
# 创建表时指定
CREATE TABLE t_user5(
id int,
name varchar(20) not null,
address varchar(20) not null
);
# 创建表之后指定
alter table t_user6 modify name varchar(20) not null;
alter table t_user6 modify address varchar(20) not null;
# 删除约束
alter table t_user6 modify name varchar(20);
alter table t_user6 modify address varchar(20);

唯一约束

# 如果不给唯一约束命名,就默认和列名相同
# 唯一约束的列上默认会创建一个唯一索引
# 该列中的数据不能重复,允许为null
create table t_user2(
id int,
name varchar(20),
phone_number varchar(20) unique
);
# 也可以这样声明
create table t_user2(
id int,
name varchar(20),
phone_number varchar(20),
# constraint constraint_name可以省略
# 以下这两种方式都可以
unique(phone_number)
constraint uk_phone unique(phone_number)
);

# 建表后指定
alter table t_user2 add constraint uk_phone unique(phonoe_number);
alter table t_user2 modify phone_number varchar(20) unique;

# 删除唯一约束
# 添加唯一约束的列上会自动创建唯一索引
# 删除唯一约束只能通过删除唯一索引的方式来删除
# 删除时需指定唯一索引名,唯一索引名和唯一约束名一样
alter table t_user2 drop index xxx;

主键约束

# 主键唯一标识所在行
# primary key,一张表只能有一个主键
# mysql的主键约束名总是PRIMARY(自己命名无效)
# 创建主键约束时,系统默认在所在的列或列组合上建立主键索引
# 删除主键约束,主键索引也删除了
# 主键约束的列,这一列上的数据非空且不能重复(not null + unique)
create table emp1(
eid int primary key,
name varchar(20),
depId int,
salary double
);
# 联合主键,这个主键是由多个列的字段组成
# 主键是由 name和depId 这两个字段共同组成的
# 这两个字段共同唯一标识所在行
create table emp3(
name varchar(20),
depId int,
salary double,
primary key(name,depId)
);
# 也可以不同时写
create table emp5(
eid int,
name varchar(20),
depId int,
salary double
);
alter table emp5 add primary key(name,deptId);
# 删除主键
alter table 表名 drop primary key;
# 删除主键约束后,非空还在

自增长约束

# 一个表中最多只能有一个自增长列
# 自增长约束的列必须是主键或者唯一约束
# 自增长约束的字段必须是整数类型
# 如果自增列指定了0和null,会在当前最大值的基础上自增
create table t_user2(
id int primary key auto_increment,
name varchar(20)
)auto_increment = 100;
# id的值从100开始
insert into t_user2 values(null,'sha_gua_a');
# 也可以在创建表之后指定
create table t_user3(
id int primary key auto_increment,
name varchar(20)
);
alter table t_user3 auto_increment = 200;
-- or
alter table tb_name modify column_name dataType auto_increment;
# 删除
alter table tb_name modify column_name dataType;
# mysql5.7中
# 对于自增主键的分配原则,是由innodb数据字典内部一个计数器决定的,该计数器只在内存中维护
# 并不会持久化到磁盘中,数据库重启时,该计数器会被初始化
# mysql8中持久化到了重做日志中

外键约束

# 限定某个表的某个字段的引用完整性
# 外键字段不能作为该表的主键
# 从表的外键列必须引用主表的主键或唯一约束的列
# 向从表添加数据时,外键列的值不能随便写,必须参照主表的被参照列
# 创建外键约束时,默认是自动生成的外键名
# 先创建主表,再创建从表
# 先删除从表,再删除主表
# 当主表的记录被从表参照时,主表的记录不允许被删除
# 从表的外键列和主表被参照的列数据类型必须一样
# 创建外键约束时,默认会在所在的列上建立对应的普通索引,索引名是列名
# 删除外键约束后,必须手动删除对应的索引
# 主表
create table dept(
deptno varchar(20) primary key,
name varchar(20)
);
# 从表
create table emp(															
eid varchar(20) primary key,
ename varchar(20),
dept_id varchar(20),
constraint emp_fk foreign key (dept_id) references dept(deptno)
);
# emp_fk是外键名
# 也可以分开写
alter table emp add constraint emp_fk foreign key (dept_id) references dept(deptno);
# 删除外键和索引
alter table 从表名 drop foreign key 外键约束名;
alter table 从表名 drop index 索引名;
# 约束等级
cascade:在主表上update/delete时,同步update/delete从表的依赖记录
set null:在主表上update/delete时,将从表的依赖记录设为null(从表的外键列不能为not null)
no action:从表中有对应的依赖记录,不允许对主表的被依赖项进行update/delete
restrict: 同no action,都是立即检查外键约束
默认是restrict
# 用法(更新时使用cascade,删除时使用set null)
constraint emp_fk foreign key (dept_id) references dept(deptno) on update cascade on delete set null

外键和级联更新适用于单机低并发，不适合分布式，高并发集群。
级联更新是强阻塞，存在数据库更新风暴的风险。
外键影响数据库的插入速度。

默认约束

create table t_user10(
	id int,
	name varchar(10),
	address varchar(10) default '北京'
);
# 删除默认约束
alter table t_user10 modify address varchar(10) default null;

默认auto_increment初始值是1。
每个表都可以指定一个不同的存储引擎，外键约束不能跨引擎使用。