MySQL 约束

查看约束

在这里插入图片描述

select * from information_schema.table_constraints
where   table_name='要查看的表名'

按约束的作用范围

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

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

约束的作用

not null 非空约束
unique 唯一约束
primary key 主键约束
foreign key 外键约束
check 检查约束
default 默认值约束

在这里插入图片描述

添加约束

声明表定义约束

create table 表名 (
id int not null
);

声明表之后，追加、删除约束

alter table 表名
modify 列类型约束 — 追加约束写上约束便可，若删除约束不写约束便可

sql server 追加约束

alter table 表名
add constraint 约束名约束类型（字段）

alter table 表名
add constraint ch_tel check( 列名 like [0-9] [0-9][0-9][0-9] )

非空约束

声明表定义约束

create table 表名 (
id int not null
);
声明之后追加约束
alter table 表名
modify 列类型 not null— 追加约束写上约束便可，若删除约束不写约束便可

删除非空约束

alter table 表名
modify 列类型 — 除去 not null 便可

唯一约束 UNIQUE

在这里插入图片描述

同一个表可以有多个唯一约束
唯一约束可以是某一列的值唯一，也可以多个列组合的值唯一(复合约束)
唯一约束允许列值为空，主键约束是特殊的唯一约束，只是不允许为 null，即主键约束（primary key）相当于唯一约束+非空约束组合，主键约束列不允许重复，且不能出现空值 null
在创建唯一约束的时候，若没有给唯一约束命名，则默认和列名相同。复合唯一约束，没命名，也取复合列中的第一个列做为约束名
Mysql 会给唯一约束的列上默认创建一个唯一索引， SQL server 中主键约束，会默认创建一个唯一索引

在create table 添加唯一约束

create table 表名(
sut_NO char(8) UNIQUE, – 列级约束
sut_ID char(18), – , 别忘记
constraint 约束名 UNIQUE(sut_ID ) 可以简写 UNIQUE KEY （sut_ID） —表级约束
);

在create table 添加复合唯一约束

create table 表名(
sut_NO char(8) , – 列级约束
sut_ID char(18), – , 别忘记
constraint 约束名 UNIQUE(sut_ID ，sut_NO) 可以简写 UNIQUE KEY （sut_ID,sut_NO） —表级约束
);

建表后指定唯一约束

方式1 支持复合约束的
alter table 表名 add unique key(字段列表)

方式2 不支持复合约束
alter table 表名 modify 字段名 unique

删除唯一约束
在这里插入图片描述

alter table 表名
DROP INDEX 索引名

查看表的索引
show index from 表名；

主键约束 PRIMARY KEY

主键约束：唯一约束+ 非空约束非空且唯一
在这里插入图片描述

create table 主键约束

create table student (
id int autoincrement，
name   varchar(10),
stu_ID  char(18)  primary key   -- 列级约束----身份证号  


create table student (
id int ，
name   varchar(10),
stu_ID  char(18) ，
--   MYSQL   主键名总是PRIMARY ，就算命名了主键约束名，也没用
constraint  约束名 primary key（stu_ID）   -- 列级约束----身份证号

建表后指定唯一约束

alter table 表名
add primary key （字段、字段）；

删除主键约束

alter table 表名
drop primary key；

自增列 AUTO_INCREMENT

在这里插入图片描述
自增列的特点

一个表最多只能有一个自增长列，SQLServer 也是这样，同一个表，后面列设置Identity，前面设置的就自动取消了。
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列），否则会出现上面的错误。
自增约束的列的数据类型必须是整数类型
如果自增列指定了0和null，会在当前最大值的基数上自增；如果自增列手动指定具体值，直接赋值为具体的值 .[ insert ]

create table 自增列

create table student (
id int  primary key auto_increment，
name   varchar(10))

建表后指定自增列

alter table 表名
modify 列名数据类型 auto_increment；

删除自增列

alter table 表名
modify 列名数据类型；

sql server identity **

要想显示的为某表的标识列显示添加数据，在insert语句中一定要在表后面显示**要添加的字段，即使是所有字段，也不能省略。

如：Table_1(ID,score,name)，如果只写Table_1仍然不能为ID显示的添加数据。

注意2：插入完数据要将identity_insert设置为OFF。

若不关闭，则再次添加数据时，无法自增长，提示错误信息：

set identity_insert 表名 ON
insert into 表名(列，。。。列)
values（值，。。。。值）
set identity_insert 表名 OFF

自增列 mysql 5.7 与 mysql 8.0 系列区别

MySQL 5.7 自增主键 Auto_increment 的值如果大于表中的最大值+1，在mysql重启之后，会重置Auto_increment值 modify 列名数据类型现有表中最大值+1 （就删除数据，导致内存中表的auto_increment > 表中最大的自增列的值+1，重启mysql，内存中auto_increment 就会消失，会根据获取现有表中自增列的最大值+1，放在内存作为此表的 auto_increment 值），这种现象在某些情况下，会导致业务主键冲突或者其他难以发现的问他

MySQL 8.0 系列 将自增主键的计数器持久化到 重做日志 中。每次计算器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

在这里插入图片描述

create table  表名(
id int auto_increment primary key,
name char(10)
) engine innodb  auto_increment=100  default charset 'utf8'

或者首条记录指明自增列的值，后续就 +1了
insert into 表名
values(300,‘zen’);
insert into 表名 (name)
values(‘zen’); //id jius 301

alter table 表名
modify 字段类型 Auto_increment;
alter table 表名
modify 字段类型 primary key Auto_increment;

外键约束 Foreign key

**sql server **
在这里插入图片描述

主表：有外键对应的表，就是主表，或称父表
从表：有外键的表，就算从表，或称子表
城市表（主表），学生表（从表），学生表中的户籍地址外键，对应城市表的主键。
注意外键对应的在主表中，必须是主键或者唯一键

Mysql
外键的特性：

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

create table 创建外键
在这里插入图片描述

主表
create table department(
dept_id char(10) primary key,
dept_name varchar(15));

从表
create table employee(
emp_no char(6) primary key ,
department_id chart(10),
【constraint FK_emp_dept】 foreign key (department_id) references department(dept_id));

alter table 创建外键

主表
create table department(
dept_id char(10) primary key,
dept_name varchar(15));

从表
create table employee(
emp_no char(6) primary key ,
department_id chart(10)
);

alter table employee
add 【constraint FK_emp_dept】 foreign key (department_id) references department(dept_id);

删除外键
在这里插入图片描述

1.首先要查看约束
select * from information_schema.table_constraints
where table_name = ‘表名’；

删除外键约束
alter table 表名
drop foreign key 外键约束名； – drop primary key 删除主键约束

查看表的索引
show index from 表名；

删除索引
alter table 表名
drop index 索引名；

约束等级

Cascade 方式：在父表上 updae | delete 数据时，同步 Update | Delete 子表的匹配记录
Set null 方式：在父表上update/delete记录时，将子表上匹配记录的列设为null要注意子表的外键列不能为not null
No action 方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict 方式：同no action, 都是立即检查外键约束

主表
create table department(
dept_id char(10) primary key,
dept_name varchar(15));

从表
create table employee(
emp_no char(6) primary key ,
department_id chart(10),
【constraint FK_emp_dept】 foreign key (department_id) references department(dept_id) on update cascade on
);

外键创建的规定

在 mySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。比如大型网址中的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MYSQL 允许你不使用系统自带的外键约束，在 应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，保持数据的一致性、

【阿里开发规范】
【强制】不得使用外键与级联，一切外键概念必须在应用层解决
说明：学生表中的Student_id 是主键，那么成绩表中的student_id 则为外键。如果更新学生表中的 student_id ,同时触发成绩表中的student_id 更新，即为级联更新。外键与级联更新适用于 单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库【更新风暴】的风险；外键影响数据库的插入速度。

外键约束（foreign key）不能跨引擎使用
mySQL 支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参考完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全的随意的