MYSQL回顾（完整性约束相关）
简介
MYSQL完整性约束条件主要包括:
- auto_increment
- not null 和 default
- unique
- primary key
- foreign key
unique、 primary key、not null、default相对简单，本篇文章不做记录。
auto_increment
被指定为自增长的字段必须是key 比如primary key 被指定为自增长的字段默认从1开始，默认步长为1 被指定为自增长的字段在插入数据时可以不指定该字段值.(如下 id为主键为自增长)
mysql> desc article;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| title | varchar(80) | YES | | NULL | |
| createTime | timestamp | YES | | NULL | |
| img | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert article(title) value("数据结构");
Query OK, 1 row affected (0.01 sec)了解即可
%模糊匹配查询任意长度任意字符的变量
mysql> show variables like "auto_inc%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)auto_increment_increment为自增长步长 默认为1 auto_increment_offset 为自增长的起始偏移量 默认为1即第一条记录的id
设置步长
设置会话级别步长
mysql> set session auto_increment_increment=5;设置全局级别步长
注意：设置全局级别步长需要退出本次会话再次登录才生效
mysql> set global auto_increment_increment=5;设置起始偏移量
设置会话级别起始偏移量
mysql> set session auto_increment_offset=3;设置全局级别起始偏移量 注意：设置全局级别起始偏移量需要退出本次会话再次登录才生效
mysql> set global auto_increment_offset=3;不连续主键
+----+--------------+------------+
| id | title | createTime |
+----+--------------+------------+
| 1 | NULL | NULL |
| 3 | 操作系统 | NULL |
+----+--------------+——————+在主键id为1的的记录后面插入了不连贯的一条记录，那么后续自增的记录将是在id为3的基础上自增，即跳过了2；除非手动指定某一条记录的id为2;
mysql> insert into article(title) values("编译原理");
执行上面命令结果如下:
mysql> select * from article;
+----+--------------+------------+
| id | title | createTime |
+----+--------------+------------+
| 1 | NULL | NULL |
| 3 | 操作系统 | NULL |
| 4 | 编译原理 | NULL |
+----+--------------+——————+可以手动插入id为2的记录：
mysql> insert into article(id, title) values(2, "计算机基础”);
mysql> select * from article;
+----+-----------------+------------+------+
| id | title | createTime | img |
+----+-----------------+------------+------+
| 1 | NULL | NULL | NULL |
| 2 | 计算机基础 | NULL | NULL |
| 3 | 操作系统 | NULL | NULL |
| 4 | 编译原理 | NULL | NULL |
+----+-----------------+------------+------+foreign key
Foreign key是为了建立表之间的关系，为关联不同的表而生 因为真实的业务中不可能只有一张表。所有的信息都存在于一张表是不是不可以，但是存在于一张表中必然有重复内容，极其浪费空间资源（比如员工和部门，每个员工都有一个对应的部门，一对多关系，有多个员工就要给每个员工分配一个空间存储他的部门信息，相同部门的员工部门信息必然是重复的），也不便于数据统一维护（比如修改某个部门的名字，需要把所有属于该部门的员工记录的部门信息都要修改一遍）
1.创建表
mysql> create table emp(
-> id int not null primary key,
-> name char(10),
-> sex enum('male', 'female'),
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> );
ERROR 1824 (HY000): Failed to open the referenced table 'dep'注意：以上创建emp员工表时关联外键失败，外键指向了dep表的id，因为还不存在dep这张部门表，导致关联失败
所以需要先创建被关联的表（dep）再创建关联表(emp)
先创建被关联表（部门表）
mysql> create table dep(
-> id int primary key auto_increment,
-> name char(20)
-> );
Query OK, 0 rows affected (0.02 sec)再创建关联表（员工表）
mysql> create table emp(
-> id int not null primary key auto_increment,
-> name char(20),
-> sex enum('male', 'female'),
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> );
Query OK, 0 rows affected (0.02 sec)注意：外键在被关联表的中一定是唯一的(unique 或 primary key) 需要将dep表中的id设置为唯一 比如 unique 或 primary key，否则会报错
2.插入数据
插入数据的时候先向被关联表中插入记录 再向关联表中插入记录 如果直接向关联表中插入记录，如果外键在被关联表中不存在会导致插入失败
3.删除数据
先删除关联表中的记录
delete from emp where dep_id=1;再删除被关联表中的记录
delete from dep where id=1;反过来执行会报错，因为关联表（员工表）中还存在一些记录的外键指向被关联表（部门表），所以直接删除被关联表（部门表）的记录时不合法的。
4.更新数据
强行更新被关联表中的记录的主键也会报错，因为关联表中还存在一些记录的外键指向被关联表
update dep set id=333 where id=3;解决方案
创建关联表（员工表）的时候增加删除同步和更新同步
on delete cascade
on update cascademysql> create table emp(
-> id int not null primary key,
-> name char(10),
-> sex enum('male', 'female'),
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> on delete cascade
-> on update cascade
-> );
Query OK, 0 rows affected (0.02 sec)注意：下图红框中换行无逗号 因为同属于一句
image.png
增加了删除同步和更新同步，建表方面：建表时还是需要先建立被关联表再建关联表。插入记录方面：还是要先向被关联表中插入记录，再向关联表中插入记录。
删除同步和更新同步并不解决建表和插入记录操作的先后问题 删除同步是指删除被关联表的记录同步更新关联表 更新同步是指更新被关联表的记录同步更新关联表
增加了删除同步和更新同步后，更新部门表中的记录和删除部门表的记录不再会报错，会同步更新或删除员工表中的记录
尽量不要使用foreign key
建议：尽量不要使用foreign key，foreign key会限制表之间的关系，表之间的关联关系尽量在程序层面去维护，使用代码去维护表之间的这种关联关系（更新同步、删除同步）
设置外键时可以通过constraint给外键起一个名字（可选）
image.png