[TOC]

一、表的完整性约束目的

• 为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

• 约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

  • not null : 非空约束，指定某列不能为空
  • auto_increment : 自增约束，只用在int型
  • unique : 字段唯一性约束，指定某列或几列的数据不能重复
  • primary key ： 主键，指定该列的值可以唯一地标识该列记录
  • forrign key ： 外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

二、not null

是否可空，null表示空，非字符串

not null - 不可空

null - 可空

2.1 not null 实例

11.创建t12表 id字段约束不为空
2mysql> create table t12 (id int not null);
3Query OK, 0 rows affected (0.02 sec)
4
52.查看t12表中所有字段记录
6mysql> select * from t12;
7Empty set (0.00 sec)
8
93.显示t12表的结构
10mysql> desc t12;
11+-------+---------+------+-----+---------+-------+
12| Field | Type    | Null | Key | Default | Extra |
13+-------+---------+------+-----+---------+-------+
14| id    | int(11) | NO   |     | NULL    |       |
15+-------+---------+------+-----+---------+-------+
16row in set (0.00 sec)
17
184.不能向id列插入空元素，插入的是空值
19mysql> insert into t12 values (null);
20ERROR 1048 (23000): Column 'id' cannot be null
21
225.向t12表中插入正确数据
23mysql> insert into t12 values (1);
24Query OK, 1 row affected (0.01 sec)

2.2 default

我们约束了某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

2.3 not null + default实例

11.创建t13表 id1字段约束不为空，id2字段约束不为空且默认值为222
2mysql> create table t13 (id1 int not null,id2 int not null default 222);
3Query OK, 0 rows affected (0.01 sec)
4
52.显示t13表结构
6mysql> desc t13;
7+-------+---------+------+-----+---------+-------+
8| Field | Type    | Null | Key | Default | Extra |
9+-------+---------+------+-----+---------+-------+
10| id1   | int(11) | NO   |     | NULL    |       |
11| id2   | int(11) | NO   |     | 222     |       |
12+-------+---------+------+-----+---------+-------+
13rows in set (0.01 sec)
14
153.只向id1字段添加值，会发现id2字段会使用默认值填充
16mysql> insert into t13 (id1) values (111);
17Query OK, 1 row affected (0.00 sec)
18
194.显示当前表的记录
20mysql> select * from t13;
21+-----+-----+
22| id1 | id2 |
23+-----+-----+
24| 111 | 222 |
25+-----+-----+
26row in set (0.00 sec)
27
285.id1字段不能为空，所以不能单独向id2字段填充值；
29mysql> insert into t13 (id2) values (223);
30ERROR 1364 (HY000): Field 'id1' doesn't have a default value
31
326.向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
33mysql> insert into t13 (id1,id2) values (112,223);
34Query OK, 1 row affected (0.00 sec)
35mysql> select * from t13;
36+-----+-----+
37| id1 | id2 |
38+-----+-----+
39| 111 | 222 |
40| 112 | 223 |
41+-----+-----+
42rows in set (0.00 sec)

2.4 not null 不生效

1设置严格模式：
2    不支持对not null字段插入null值
3    不支持对自增长字段插入”值
4    不支持text字段有默认值
5
6直接在mysql中生效(重启失效):
7mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
8
9配置文件添加(永久失效)：
10sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

三、auto_increment（自增）

• 设置字段的值在没有被赋值时自增，只用于int型，并且字段必须设置为键字段

• 被约束的字段必须同时被key约束

• 一个表只能由一个自增字段

3.1 实例

11.创建学生表不指定id，则自动增长
2create table student(
3id int unique auto_increment,
4name varchar(20),
5sex enum('male','female') default 'male'
6);
7
8mysql> desc student;
9+-------+-----------------------+------+-----+---------+----------------+
10| Field | Type                  | Null | Key | Default | Extra          |
11+-------+-----------------------+------+-----+---------+----------------+
12| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
13| name  | varchar(20)           | YES  |     | NULL    |                |
14| sex   | enum('male','female') | YES  |     | male    |                |
15+-------+-----------------------+------+-----+---------+----------------+
16mysql> insert into student(name) values
17    -> ('cecilia'),
18    -> ('xichen')
19    -> ;
20
21mysql> select * from student;
22+----+----------+------+
23| id | name     | sex  |
24+----+----------+------+
25|  1 | cecilia  | male |
26|  2 | xichen   | male |
27+----+----------+------+
28
29
302. 也可以指定id
31mysql> insert into student values(4,'asb','female');
32Query OK, 1 row affected (0.00 sec)
33
34mysql> insert into student values(7,'wsb','female');
35Query OK, 1 row affected (0.00 sec)
36
37mysql> select * from student;
38+----+---------+--------+
39| id | name    | sex    |
40+----+---------+--------+
41|  1 | cecilia | male   |
42|  2 | xichen  | male   |
43|  4 | asb     | female |
44|  7 | wsb     | female |
45+----+---------+--------+
46
47
483. 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
49mysql> delete from student;
50Query OK, 4 rows affected (0.00 sec)
51
52mysql> select * from student;
53Empty set (0.00 sec)
54
55mysql> insert into student(name) values('ysb');
56mysql> select * from student;
57+----+------+------+
58| id | name | sex  |
59+----+------+------+
60|  8 | ysb  | male |
61+----+------+------+
62
634. 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
64mysql> truncate student;
65Query OK, 0 rows affected (0.01 sec)
66
67mysql> insert into student(name) values('xichen');
68Query OK, 1 row affected (0.01 sec)
69
70mysql> select * from student;
71+----+--------+------+
72| id | name   | sex  |
73+----+--------+------+
74|  1 | xichen | male |
75+----+--------+------+
76row in set (0.00 sec)

四、unique（唯一键）

唯一约束，指定某列或者几列组合不能重复。

4.1 unique实例

1方法一：
2create table t1(
3id int,
4name varchar(20) unique,
5course varchar(100)
6);
7
8
9方法二：
10create table department2(
11id int,
12name varchar(20),
13course varchar(100),
14unique(name)
15);
16
17
18mysql> insert into t1 values(1,'xichen','计算机');
19Query OK, 1 row affected (0.00 sec)
20mysql> insert into t1 values(1,'xichenT','计算机'); # 此时会报错
21ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

4.2 联合唯一

• ip在port不同时，可以相同，ip不同时port也可以相同，均合法

• ip和port都相同时，就是重复数据，不合法

  mysql>: create table tu1 ( ip char(16), port int, unique(ip, port)# 联合唯一 );

  插入正确数据

  mysql> insert into service values -> ('192.168.0.10',8080), -> ('192.168.0.20',8080), -> ('192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0

  插入重复数据 (ip,poor和已有的记录重复了)

  mysql> insert into service(name,host,port) values('192.168.0.10',8080); ERROR 1062 (23000): Duplicate entry '192.168.0.10-8080' for key 'host'

五、primary key（主键）

• 表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段
• 主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
• 主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
• 主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
• 主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

5.1 单字段做主键

1#方法一：not null+unique
2create table t1(
3id int not null unique, #主键 默认找第一个设为唯一键的字段
4name varchar(20) not null unique,
5course varchar(100)
6);
7
8mysql> desc t1;
9+---------+--------------+------+-----+---------+-------+
10| Field   | Type         | Null | Key | Default | Extra |
11+---------+--------------+------+-----+---------+-------+
12| id      | int(11)      | NO   | PRI | NULL    |       |
13| name    | varchar(20)  | NO   | UNI | NULL    |       |
14| course  | varchar(100) | YES  |     | NULL    |       |
15+---------+--------------+------+-----+---------+-------+
16rows in set (0.01 sec)
17
18
19#方法二：在某一个字段后用primary key
20create table t2(
21id int primary key, #主键
22name varchar(20),
23course varchar(100)
24);
25
26mysql> desc t2;
27+---------+--------------+------+-----+---------+-------+
28| Field   | Type         | Null | Key | Default | Extra |
29+---------+--------------+------+-----+---------+-------+
30| id      | int(11)      | NO   | PRI | NULL    |       |
31| name    | varchar(20)  | YES  |     | NULL    |       |
32| course  | varchar(100) | YES  |     | NULL    |       |
33+---------+--------------+------+-----+---------+-------+
34rows in set (0.00 sec)
35
36#方法三：在所有字段后单独定义primary key
37create table t3(
38id int,
39name varchar(20),
40course varchar(100),
41primary key(id); #字段id设为主键
42
43mysql> desc t3;
44+---------+--------------+------+-----+---------+-------+
45| Field   | Type         | Null | Key | Default | Extra |
46+---------+--------------+------+-----+---------+-------+
47| id      | int(11)      | NO   | PRI | NULL    |       |
48| name    | varchar(20)  | YES  |     | NULL    |       |
49| course | varchar(100) | YES  |     | NULL    |       |
50+---------+--------------+------+-----+---------+-------+
51rows in set (0.01 sec)
52
53# 方法四：给已经建成的表添加主键约束
54mysql> create table t4(
55    -> id int,
56    -> name varchar(20),
57    -> course varchar(100));
58Query OK, 0 rows affected (0.01 sec)
59
60mysql> desc t4;
61+---------+--------------+------+-----+---------+-------+
62| Field   | Type         | Null | Key | Default | Extra |
63+---------+--------------+------+-----+---------+-------+
64| id      | int(11)      | YES  |     | NULL    |       |
65| name    | varchar(20)  | YES  |     | NULL    |       |
66| course  | varchar(100) | YES  |     | NULL    |       |
67+---------+--------------+------+-----+---------+-------+
68rows in set (0.01 sec)
69
70# 给已经建成的表添加主键约束
71mysql> alter table t4 modify id int primary key;
72Query OK, 0 rows affected (0.02 sec)
73Records: 0  Duplicates: 0  Warnings: 0
74
75mysql> desc t4;
76+---------+--------------+------+-----+---------+-------+
77| Field   | Type         | Null | Key | Default | Extra |
78+---------+--------------+------+-----+---------+-------+
79| id      | int(11)      | NO   | PRI | NULL    |       |
80| name    | varchar(20)  | YES  |     | NULL    |       |
81| course  | varchar(100) | YES  |     | NULL    |       |
82+---------+--------------+------+-----+---------+-------+
83rows in set (0.01 sec)

5.2 多字段做主键(主键唯一)

1# 创建多字段做主键（ip，port）
2create table t1(
3ip varchar(15),
4port char(5),
5name varchar(10) not null,
6primary key(ip,port)
7);
8
9
10mysql> desc service;
11+--------------+-------------+------+-----+---------+-------+
12| Field        | Type        | Null | Key | Default | Extra |
13+--------------+-------------+------+-----+---------+-------+
14| ip           | varchar(15) | NO   | PRI | NULL    |       |
15| port         | char(5)     | NO   | PRI | NULL    |       |
16| name         | varchar(10) | NO   |     | NULL    |       |
17+--------------+-------------+------+-----+---------+-------+
18rows in set (0.00 sec)
19
20# 插入两条数据
21mysql> insert into t1 values
22    -> ('172.16.45.10','3306','mysqld'),
23    -> ('172.16.45.11','3306','mariadb')
24    -> ;
25Query OK, 2 rows affected (0.00 sec)
26Records: 2  Duplicates: 0  Warnings: 0
27
28mysql> insert into t1 values ('172.16.45.10','3306','nginx');
29ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

5.3 主键和唯一键分析

11.x为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
2mysql>: create table t1 (x int unique auto_increment, y int unique);
3    
42.y为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
5mysql>: create table t2 (x int unique, y int unique auto_increment);
6    
73.x为主键：设置了主键就是设置的，主键没设置自增，那自增是可以设置在唯一键上的
8mysql>: create table t3 (x int primary key, y int unique auto_increment);
9    
104.x为主键：设置了主键就是设置的，主键设置了自增，自增字段只能有一个，所以唯一键不能再设置自增了
11mysql>: create table t4 (x int primary key auto_increment, y int unique);
12    
135.默认主键：没有设置主键，也没有 唯一自增键，那系统会默认添加一个 隐式主键（不可见）
14mysql>: create table t5 (x int unique, y int unique);

六、foreign key（外键）

foreign key：指定该行记录从属于主表中的一条记录，主要用于参照完整性

重点：外键本身可以不唯一，但是关联的字段必须是唯一的

6.1 语法

foreign 主表字段名 references 被关联表名/从表名（字段名）

6.2 创建外键实例

6.2.1 一对一的表关系设置外键(foreign key)

假设我们要描述所有作者，需要描述的属性有：作者id号，姓名，联系方式，性别，作者详细信息（详细信息info，地址address）、由于作者的详细信息我们需要重复的存储信息，而我们都知道详细信息都很长我们不可能那字段去存储它

所以：我们可以定义另外一个作者详细信息表，然后让作者基本信息表关联作者详细信息表，如何关联即 foreign key

下面就是我们利用外键来建立一对一的关联表

作者表author的属性：id，name，mobile，sex，age，detail_id

作者详细信息表author_detail属性：id，info，address

一、错误案例

1# 1.创建表不成功，原因是我们创建外键foreign key时，要先创建被关联的表(从表)author_detail
2mysql> create table author(
3    -> id int primary key auto_increment,
4    ->     name varchar(64) not null,
5    ->     mobile char(11) unique not null,
6    ->     sex enum('男', '女') default '男',
7    ->     age int default 0,
8    ->     detail_id int  not null,
9    ->     foreign key(detail_id) references author_detail(id)
10    -> );
11ERROR 1215 (HY000): Cannot add foreign key constraint
12
13# 出错案例
14# 2.创建的被关联表的字段没有这只唯一性约束
151.先创建被关联的表(从表)author_drtail ,可以创建成功
16mysql> create table author_detail(
17    -> id int ,
18    ->     info varchar(256),
19    ->     address varchar(256)
20    -> );
21Query OK, 0 rows affected (0.40 sec)
222.在创建关联的表(主表)author
23# 会创建不成功，因为所关联表的字段没有设置唯一性约束！
24mysql> create table author(
25    -> id int primary key auto_increment,
26    ->     name varchar(64) not null,
27    ->     mobile char(11) unique not null,
28    ->     sex enum('男', '女') default '男',
29    ->     age int default 0,
30    ->     detail_id int unique not null,
31    ->     foreign key(detail_id) references author_detail(id)
32    -> );
33ERROR 1215 (HY000): Cannot add foreign key constraint

二、正确案例

1.创建两个关联表(author)与被关联表(author_detail)

11.先创建被关联的表（从表）（author_detail）,可以创建成功
2mysql> create table author_detail(
3    -> id int primary key auto_increment,#被关联表设置唯一约束，为主键
4    ->     info varchar(256),
5    ->     address varchar(256)
6    -> );
7Query OK, 0 rows affected (0.43 sec)
8
92.再创建关联表（主表）（author），可以创建成功
10mysql> create table author(
11    -> id int primary key auto_increment,
12    ->     name varchar(64) not null,
13    ->     mobile char(11) unique not null,
14    ->     sex enum('男', '女') default '男',
15    ->     age int default 0,
16    ->     detail_id int unique not null,# 外键字段，设了唯一性，因为是一对一的表关系
17    ->     foreign key(detail_id) references author_detail(id)
18    -> );
19Query OK, 0 rows affected (0.63 sec)

2. 对两个表进行数据插入

1# 先插入关联表(主表author)如数据出错
21.插入数据，出现错误，要先插入被关联表的数据
3mysql>insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
4ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`author`, CONSTRAINT `author_ibfk_1` FOREIGN KEY (`detail_id`) REFERENCES `author_detail` (`id`))
5
6## 先插入被关联(author_detail)的表的数据，不会出错
71.给被关联的从表(author_detail)插入数据
8mysql>insert into author_detail(info,address)values('Tom_info','Tom_address');
9Query OK, 1 row affected (0.13 sec)
10
11mysql> insert into author_detail(info,address)values('Bob_info','Bob_address');
12Query OK, 1 row affected (0.13 sec)
13
14mysql> insert into author_detail(info,address)values('Tom_info_sup','Tom_address_sup');
15Query OK, 1 row affected (0.12 sec)
16
172.再插入关联表(主表author)的数据，不会出错
18mysql>insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
19Query OK, 1 row affected (0.13 sec)
20
21mysql>insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
22Query OK, 1 row affected (0.12 sec)
23
24
25# cmd图例
26mysql> select * from author_detail;
27+----+--------------+-----------------+
28| id | info         | address         |
29+----+--------------+-----------------+
30|  1 | Tom_info     | Tom_address     |
31|  2 | Bob_info     | Bob_address     |
32|  3 | Tom_info_sup | Tom_address_sup |
33+----+--------------+-----------------+
343 rows in set (0.00 sec)
35
36mysql> select * from author;
37+----+------+-------------+------+------+-----------+
38| id | name | mobile      | sex  | age  | detail_id |
39+----+------+-------------+------+------+-----------+
40|  1 | Tom  | 13344556677 | 男   |    0 |         1 |
41|  2 | Bob  | 15666882233 | 男   |    0 |         2 |
42+----+------+-------------+------+------+-----------+
432 rows in set (0.00 sec)

3.修改关联表(主表author)

1mysql>:update author set detail_id=3 where detail_id=2; #有没有被其他数据关联的数据，就可以修改
2
3    ## 图示例
4mysql> select * from author;
5+----+------+-------------+------+------+-----------+
6| id | name | mobile      | sex  | age  | detail_id |
7+----+------+-------------+------+------+-----------+
8|  1 | Tom  | 13344556677 | 男   |    0 |         1 |
9|  2 | Bob  | 15666882233 | 男   |    0 |         3 | # 关联表的detail已经修改了
10+----+------+-------------+------+------+-----------+
112 rows in set (0.00 sec)

4.修改被关联表(从表author_detail)

1mysql> update author_detail set id=10 where id=1;# 无法修改的，原因会在后面级联提到
2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`author`, CONSTRAINT `author_ibfk_1` FOREIGN KEY (`detail_id`) REFERENCES `author_detail` (`id`))

5.删除关联表的揭露

mysql>: delete from author where detail_id=3;  # 会直接删除

6.删除被关联表中记录

1mysql> delete from author_detail where id=1; # 无法删除的
2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`author`, CONSTRAINT `author_ibfk_1` FOREIGN KEY (`detail_id`) REFERENCES `author_detail` (`id`))
3

重点：在对于表设置外键且没有级联关系的情况下

• 表的增加操作：先增加被关联表记录，再增加关联表记录
• 表的删除操作：先删除关联表记录，再删除被关联表记录
• 表的更新操作：关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)

总结：以上我们实现的是一对一的表关系，并且是没有设置级联的，从上面的的修改和删除的部分代码我们可以看到时无法对被关联的表进行修改和删除记录操作的，后面就会详细的来讲解级联关系的表

6.2.2 一对一表关系设置外键(有级联关系)

一、设置级联关系的外键语法

1create table 关联表(主表)名(
2    字段1 数据类型[约束条件]
3    ·
4    ·
5    字段n 数据类型[约束条件]
6    foreign key(主表字段) references 被关联表名(被关联表主键字段)
7    on update cascade  # 两个表其中一个表数据更新，另一个表也跟着更新
8    on delete cascade # 两个表其中以一个数据被删除，另一个也跟着删除
9);

二、有级联关系（一对一）案例

我们依然使用上面的作者和作者详细信息的案例，并且在已知的创建表规则的条件下去完成

此处没有错误案例分析

11.先删除上面案列创建的表
2mysql>drop table author;
3mysql>drop table author_detail;
4
52.先创建被关联的表(从表author_detail)
6mysql> create table author_detail(
7    -> id int primary key auto_increment,
8    -> info varchar(256),
9    -> address varchar(256)
10    -> );
11Query OK, 0 rows affected (0.42 sec)
12
133.再创建关联表(主表author)
14mysql> create table author(
15    -> id int primary key auto_increment,
16    -> name varchar(64) not null,
17    -> mobile char(11) unique not null,
18    -> sex enum('男','女') default '男',
19    -> age int default 0,
20    -> detail_id int unique not null,
21    -> foreign key (detail_id) references author_detail(id)
22    -> on update cascade # 级联更新
23    -> on delete cascade # 级联删除
24    -> );
25Query OK, 0 rows affected (0.42 sec)
26
27
28# 插入表数据
29# 必须先插入被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
30mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);  #错误
31
321.向被关联表author_detail插入数据
33mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
34mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
35# cmd图示
36mysql> select * from author_detail;
37+----+----------+-------------+
38| id | info     | address     |
39+----+----------+-------------+
40|  1 | Tom_info | Tom_address |
41|  2 | Bob_info | Bob_address |
42+----+----------+-------------+
432 rows in set (0.00 sec)
44
452.向关联表author插入数据
46mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
47mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
48mysql> select * from author;
49# cmd图示
50+----+------+-------------+------+------+-----------+
51| id | name | mobile      | sex  | age  | detail_id |
52+----+------+-------------+------+------+-----------+
53|  1 | Tom  | 13344556677 | 男   |    0 |         1 |
54|  2 | Bob  | 15666882233 | 男   |    0 |         2 |
55+----+------+-------------+------+------+-----------+
562 rows in set (0.00 sec)
57
58
59
60# 修改关联表
611.修改关联表auhtor数据
62mysql> update author set detail_id=3 where detail_id=2; # 失败，被关联表里没有没有3对应的记录
63mysql>: update author set detail_id=1 where detail_id=2;  # 失败，1详情已被其他的作者关联
64mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
65mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
66## cmd图示
67+----+------+-------------+------+------+-----------+
68| id | name | mobile      | sex  | age  | detail_id |
69+----+------+-------------+------+------+-----------+
70|  1 | Tom  | 13344556677 | 男   |    0 |         1 |
71|  2 | Bob  | 15666882233 | 男   |    0 |         3 |
72+----+------+-------------+------+------+-----------+
732 rows in set (0.00 sec)
74
752.修改被关联表 author_detail
76mysql>: update author_detail set id=10 where id=1;  # 级联修改，同步关系关联表外键
77## cmd图示
78mysql> select * from author;
79+----+------+-------------+------+------+-----------+
80| id | name | mobile      | sex  | age  | detail_id |
81+----+------+-------------+------+------+-----------+
82|  1 | Tom  | 13344556677 | 男   |    0 |        10 |
83|  2 | Bob  | 15666882233 | 男   |    0 |         3 |
84+----+------+-------------+------+------+-----------+
852 rows in set (0.00 sec)
86
87mysql> select * from author_detail;
88+----+--------------+-----------------+
89| id | info         | address         |
90+----+--------------+-----------------+
91|  2 | Bob_info     | Bob_address     |
92|  3 | Tom_info_sup | Tom_address_sup |
93| 10 | Tom_info     | Tom_address     |
94+----+--------------+-----------------+
953 rows in set (0.00 sec)
96
97
98
99# 删除关联表author
100mysql>: delete from author where detail_id=3;  # 直接删除
101
102
103# 删除被关联表 author_detail
104mysql>: delete from author where detail_id=10;  # 可以删除对被关联表author_detail无影响
105
106mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
107mysql>: delete from author_detail where id=10;#可以删除，将关联表的记录对应的10作者详情级联删除

6.2.3 一对多表关系设置外键(有级联关系)

• 一对多的表关系，外键必须放在多的那一方，此时因为时一对多的关系，所以外键值不唯一

一、案例

以书和出版社来举例，一个书对应一个出版社，但是一个出版社可以出多本书（一对多的关系）

二、实例

此处按照正常的创建流程走，不在演示错误案例

1# 出版社（publish）：id，name，address，phone
21.先创建多的一方，也就是被级联的表
3mysql> create table publish(
4    -> id int primary key auto_increment,
5    ->     name varchar(64),
6    ->     address varchar(256),
7    ->     phone char(20)
8    -> );
9Query OK, 0 rows affected (0.39 sec)
10
11# 书(book)：id，name，price，publish_id, author_id
122. 创建一的那一方，也就是关联表
13mysql> create table book(
14    -> id int primary key auto_increment,
15    ->     name varchar(64) not null,
16    ->     price decimal(5, 2) default 0,
17    ->     publish_id int,  # 一对多的外键不能设置唯一
18    ->     foreign key(publish_id) references publish(id)
19    ->     on update cascade
20    ->     on delete cascade
21    -> );
22Query OK, 0 rows affected (0.55 sec)
23
24
25
26
27################ 对两个表插入数据
281.先增加被关联表(publish)的数据
29mysql> insert into publish(name, address, phone) values
30    -> ('人民出版社', '北京', '010-1100'),
31    -> ('西交大出版社', '西安', '010-1190'),
32    -> ('中共教育出版社', '北京', '010-1200');
33Query OK, 3 rows affected (0.10 sec)
34Records: 3  Duplicates: 0  Warnings: 0
35
362.再增加关联表(book)的数据
37mysql> insert into book(name, price, publish_id) values
38    -> ('西游记', 16.66, 1),
39    -> ('流浪记', 28.66, 1),
40    -> ('python从入门到放弃', 2.66, 2),
41    -> ('程序员修养之道', 43.66, 3),
42    -> ('好好活着', 18.88, 3);
43Query OK, 5 rows affected (0.04 sec)
44Records: 5  Duplicates: 0  Warnings: 0
45### cmd图示
46mysql> select * from book;
47+----+--------------------------+-------+------------+
48| id | name                     | price | publish_id |
49+----+--------------------------+-------+------------+
50|  1 | 西游记                   | 16.66 |          1 |
51|  2 | 流浪记                   | 28.66 |          1 |
52|  3 | python从入门到放弃        |  2.66 |          2 |
53|  4 | 程序员修养之道            | 43.66 |          3 |
54|  5 | 好好活着                 | 18.88 |          3 |
55+----+--------------------------+-------+------------+
565 rows in set (0.00 sec)
57
58mysql> select * from publish;
59+----+-----------------------+---------+----------+
60| id | name                  | address | phone    |
61+----+-----------------------+---------+----------+
62|  1 | 人民出版社            | 北京    | 010-1100 |
63|  2 | 西交大出版社          | 西安    | 010-1190 |
64|  3 | 中共教育出版社        | 北京    | 010-1200 |
65+----+-----------------------+---------+----------+
663 rows in set (0.00 sec)
67
683.没有被关联的字段，插入依旧错误
69mysql>: insert into book(name, price, publish_id) values ('流浪地球', 33.2, 4);  # 失败
70
71
72################ 更新操作
731.直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
74mysql>: update publish set id=10 where id=1;
75###cmd图示
76mysql> select * from book;
77+----+--------------------------+-------+------------+
78| id | name                     | price | publish_id |
79+----+--------------------------+-------+------------+
80|  1 | 西游记                   | 16.66 |         10 |
81|  2 | 流浪记                   | 28.66 |         10 |
82|  3 | python从入门到放弃       |  2.66 |          2 |
83|  4 | 程序员修养之道           | 43.66 |          3 |
84|  5 | 好好活着                 | 18.88 |          3 |
85+----+--------------------------+-------+------------+
865 rows in set (0.00 sec)
87
882.直接更新关联表的(book) 外键，修改的值对应被关联表(publish) 主键 如果存在，可以更新成功，反之失败
89mysql>: update book set publish_id=2 where id=4;  # 成功，此时被级联表的值是不受印象的
90mysql>: update book set publish_id=1 where id=4;  # 失败，因为外键字段没有这个值
91
92
93############ 删除操作
941.删被关联表，关联表会被级联删除
95mysql>: delete from publish where id = 2;
96
972.删关联表，被关联表不会发生变化
98mysql>: delete from book where publish_id = 3;
99
100
101# 假设：书与作者也是 一对多 关系，一个作者可以出版多本书
102create table book(
103	id int primary key auto_increment,
104    name varchar(64) not null,
105    price decimal(5, 2) default 0,
106    publish_id int,  # 一对多的外键不能设置唯一
107    foreign key(publish_id) references publish(id)
108    on update cascade
109    on delete cascade
110    
111    # 建立与作者 一对多 的外键关联
112    author_id int,  
113    foreign key(author_id) references author(id)
114    on update cascade
115    on delete cascade
116);