MySQL约束

约束

限制

是防止数据库中，存在不符合语义规定的数据，防止因错误信息的输入输出

造成无效操作或错误信息而提出

约束的方式对表数据进行额外的条件限制

---

约束的分类

根据约数据列的限制：

单列约束：每个约束只约束一列

多列约束：约束多列

根据约束的作用范围：

列级约束：只能作用在一个列上，跟在列的定义后面

表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束起的作用：

NOT NULL 非空约束，规定某个字段不能为空

UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

PRIMARY KEY 主键(非空且唯一)约束

FOREIGN KEY 外键约束

CHECK 检查约束

DEFAULT 默认值约束

在CREATE TALBE 或ALTER TABLE 时候添加约束

在ALTER TABLE 还可以删除约束

---

查看约束

第一种：

       表的索引里面

      

第二种：

       在系统自带的information_schema.table_constraints表中查看某个表的约束

                            Schema纲要                      constraint 约束、限制

---

 

非空约束

NOT NULL

只能单独限定列，只有列级约束

添加非空约束：

CREATE 或ALTER CHANGE 或 ALTER MODIFY

删除非空约束：

在ALTER中，将NOT NULL改为NULL

---

唯一性约束

UNIQUE

用来限制某个字段的值不能重复

可以是某一个列的值唯一(列级约束)，可以多个列组合的值唯一（表级约束）

不给唯一约束命名，则与字段名相同。复合约束若不起名，与第一个字段名一样

MySQL会给唯一约束的列上默认创建一个唯一索引

可以添加多个NULL值

添加唯一约束：

       CREATE 或ALTER

在字段后，表级约束写在最后，通过CONSTRAINT(约束)+UNQIUE（）

其中book2_book_name是约束名

通过ALTER进行唯一约束：add/modify/change

添加复合唯一约束：

UNIQUE(字段1，字段2...) 多个字段的组合是唯一的,

多个字段中只要有一个字段不一样，就可以添加

删除唯一约束：

通过唯一索引（约束名）方式删除 INDEX索引

括号（）前是索引名，括号里是约束的字段，最后是约束类型

---

主键约束

PRIMARY KEY

唯一标识表中的一行记录

相当于唯一约束+非空约束的组合,且会根据主键大小自动排序

列级约束和表级约束，任意字段都不允许为空，且复合不允许重复

一个表一定有一个主键约束，若不指定则MySQL自动会指定

主键的索引名固定是PRIMARY

不要修改主键字段的值，主键是数据记录的唯一标识，修改会破坏数据完整性

添加主键约束：create/alter

添加表级约束主键

建表后添加主键约束

删除主键约束：在实际中不会删除主键约束，会破环数据完整性

对id设置主键，若先添加id=100，再添加id=99，则id=99会排在结果集的前面

---

自增列

AUTO_INCREMENT

一个表只能有一个自增列

作用字段必须是整数类型

字段必须是主键列或唯一键列

若在自增列赋值0或NULL，仍会自增

创建自增列：CREATE

若给id赋值1000，则接下来自增到1001

     

建表后自增列：ALTER

删除自增约束：

---

MySQL8.0自增新特性

自增变量持久化

对自增列添加三次数据

删除最后一行记录

再次添加一次数据，此时从4开始自增

这一现象在5.7和8.0都会出现

区别在于：

5.7版本自增由计数器决定，在内存中，在数据库重启后，计数器会初始化。

把id=4记录删掉，重启后，再次自增则从3开始

8.0版本自增在重做日志中，重启后仍从日志中读取自增数值

把id=4记录删掉，重启后，再次自增则从5开始

所以叫做自增持久化

---

外键约束

FOREIGN KEY

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

A表中的字段和B表相关联

只有B表该字段存在的值，A表才能赋该值

同时也是互相约束，B表不能删除已经在A表中存在的记录

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

特点：

B表的字段必须是主键或唯一键

该约束的默认名不是列名，而是自动产生一个外键名，所以自己命名更好

创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

删表时，先删从表（或先删除外键约束），再删除主表

在“从表”中指定外键约束，并且一个表可以建立多个外键约束

从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

创建外键：

[CONSTRAINT] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)

先创建主表，外键字段必须是主键或唯一键

再创建从表

添加数据，2表外键字段赋值必须在1表中存在

建表后创建外键：ALTER

用的情况较少，一般在创建表时完成外键

外键约束等级：

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

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

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

Restrict方式 ：同no action， 都是立即检查外键约束

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

不设置，默认是No action方式

推荐设置ON UPDATE CASCADE ON DELETE RESTRICT

即主表UPDATE操作更新从表，主表DELETE操作从表修改为NULL

删除外键约束：

第一步，查看表的外键约束

SELECT * FROM information_schema.table_constraints

WHERE table_name = '表名';

删除外键约束名

第二步，查看索引名和删除索引（索引要手动删除）

查看索引

删除索引

外键约束实际开发中

不建议使用

更应该在应用层进行约束，即在Java程序中进行限定

外键约束消耗系统资源，导致系统开销变慢

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

---

CHECK约束

检查某个字段的值是否符合自己规定的要求

一般是值的范围

MySQL8.0新的支持约束

创建CHECK约束：

限制year大于18，且限制性别的值

报错violated违反

在创建表后CHECK约束：“

删除约束

---

DEFAULT约束

给字段设置默认值

插入数据若没有赋值，则为默认值

创建DEFAULT约束：

插入值

 

创建表后约束：

再添加一个字段，并约束DEFAULT

默认空字段

DEFAULT 设置为0或者 ‘ ‘空字段能够防止为NULL，影响运算

删除DEFAULT