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 cascade

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)

    -> on delete cascade

    -> on update cascade

    -> );

Query OK, 0 rows affected (0.02 sec)

注意：下图红框中换行无逗号因为同属于一句

image.png

增加了删除同步和更新同步，建表方面：建表时还是需要先建立被关联表再建关联表。插入记录方面：还是要先向被关联表中插入记录，再向关联表中插入记录。

删除同步和更新同步并不解决建表和插入记录操作的先后问题
删除同步是指删除被关联表的记录同步更新关联表
更新同步是指更新被关联表的记录同步更新关联表

增加了删除同步和更新同步后，更新部门表中的记录和删除部门表的记录不再会报错，会同步更新或删除员工表中的记录

尽量不要使用foreign key

建议：尽量不要使用foreign key，foreign key会限制表之间的关系，表之间的关联关系尽量在程序层面去维护，使用代码去维护表之间的这种关联关系（更新同步、删除同步）

设置外键时可以通过constraint给外键起一个名字（可选）