132 MySQL表的完整性约束
一、表的完整性约束目的
-
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
-
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
- not null : 非空约束，指定某列不能为空
- auto_increment : 自增约束，只用在int型
- unique : 字段唯一性约束，指定某列或几列的数据不能重复
- primary key ： 主键，指定该列的值可以唯一地标识该列记录
- forrign key ： 外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
二、not null
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
2.1 not null 实例
1.创建t12表 id字段约束不为空
mysql> create table t12 (id int not null);
Query OK, 0 rows affected (0.02 sec)
2.查看t12表中所有字段记录
mysql> select * from t12;
Empty set (0.00 sec)
3.显示t12表的结构
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
row in set (0.00 sec)
4.不能向id列插入空元素，插入的是空值
mysql> insert into t12 values (null);
ERROR 1048 (23000): Column 'id' cannot be null
5.向t12表中插入正确数据
mysql> insert into t12 values (1);
Query OK, 1 row affected (0.01 sec)
2.2 default
我们约束了某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。
2.3 not null + default实例
1.创建t13表 id1字段约束不为空，id2字段约束不为空且默认值为222
mysql> create table t13 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.01 sec)
2.显示t13表结构
mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | NO | | NULL | |
| id2 | int(11) | NO | | 222 | |
+-------+---------+------+-----+---------+-------+
rows in set (0.01 sec)
3.只向id1字段添加值，会发现id2字段会使用默认值填充
mysql> insert into t13 (id1) values (111);
Query OK, 1 row affected (0.00 sec)
4.显示当前表的记录
mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
row in set (0.00 sec)
5.id1字段不能为空，所以不能单独向id2字段填充值；
mysql> insert into t13 (id2) values (223);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value
6.向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
mysql> insert into t13 (id1,id2) values (112,223);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 112 | 223 |
+-----+-----+
rows in set (0.00 sec)
2.4 not null 不生效
设置严格模式：
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值
直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
三、auto_increment（自增）
-
设置字段的值在没有被赋值时自增，只用于int型，并且字段必须设置为键字段
-
被约束的字段必须同时被key约束
-
一个表只能由一个自增字段
3.1 实例
1.创建学生表不指定id，则自动增长
create table student(
id int unique auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
-> ('cecilia'),
-> ('xichen')
-> ;
mysql> select * from student;
+----+----------+------+
| id | name | sex |
+----+----------+------+
| 1 | cecilia | male |
| 2 | xichen | male |
+----+----------+------+
2. 也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+---------+--------+
| id | name | sex |
+----+---------+--------+
| 1 | cecilia | male |
| 2 | xichen | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+---------+--------+
3. 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+
4. 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(name) values('xichen');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | xichen | male |
+----+--------+------+
row in set (0.00 sec)
四、unique（唯一键）
唯一约束，指定某列或者几列组合不能重复。
4.1 unique实例
方法一：
create table t1(
id int,
name varchar(20) unique,
course varchar(100)
);
方法二：
create table department2(
id int,
name varchar(20),
course varchar(100),
unique(name)
);
mysql> insert into t1 values(1,'xichen','计算机');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(1,'xichenT','计算机'); # 此时会报错
ERROR 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 单字段做主键
#方法一：not null+unique
create table t1(
id int not null unique, #主键 默认找第一个设为唯一键的字段
name varchar(20) not null unique,
course varchar(100)
);
mysql> desc t1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| course | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
#方法二：在某一个字段后用primary key
create table t2(
id int primary key, #主键
name varchar(20),
course varchar(100)
);
mysql> desc t2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| course | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)
#方法三：在所有字段后单独定义primary key
create table t3(
id int,
name varchar(20),
course varchar(100),
primary key(id); #字段id设为主键
mysql> desc t3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| course | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
# 方法四：给已经建成的表添加主键约束
mysql> create table t4(
-> id int,
-> name varchar(20),
-> course varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| course | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
# 给已经建成的表添加主键约束
mysql> alter table t4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| course | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
5.2 多字段做主键(主键唯一)
# 创建多字段做主键（ip，port）
create table t1(
ip varchar(15),
port char(5),
name varchar(10) not null,
primary key(ip,port)
);
mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | char(5) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
# 插入两条数据
mysql> insert into t1 values
-> ('172.16.45.10','3306','mysqld'),
-> ('172.16.45.11','3306','mariadb')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1 values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
5.3 主键和唯一键分析
1.x为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table t1 (x int unique auto_increment, y int unique);
2.y为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table t2 (x int unique, y int unique auto_increment);
3.x为主键：设置了主键就是设置的，主键没设置自增，那自增是可以设置在唯一键上的
mysql>: create table t3 (x int primary key, y int unique auto_increment);
4.x为主键：设置了主键就是设置的，主键设置了自增，自增字段只能有一个，所以唯一键不能再设置自增了
mysql>: create table t4 (x int primary key auto_increment, y int unique);
5.默认主键：没有设置主键，也没有 唯一自增键，那系统会默认添加一个 隐式主键（不可见）
mysql>: 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.创建表不成功，原因是我们创建外键foreign key时，要先创建被关联的表(从表)author_detail
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(64) not null,
-> mobile char(11) unique not null,
-> sex enum('男', '女') default '男',
-> age int default 0,
-> detail_id int not null,
-> foreign key(detail_id) references author_detail(id)
-> );
ERROR 1215 (HY000): Cannot add foreign key constraint
# 出错案例
# 2.创建的被关联表的字段没有这只唯一性约束
1.先创建被关联的表(从表)author_drtail ,可以创建成功
mysql> create table author_detail(
-> id int ,
-> info varchar(256),
-> address varchar(256)
-> );
Query OK, 0 rows affected (0.40 sec)
2.在创建关联的表(主表)author
# 会创建不成功，因为所关联表的字段没有设置唯一性约束！
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(64) not null,
-> mobile char(11) unique not null,
-> sex enum('男', '女') default '男',
-> age int default 0,
-> detail_id int unique not null,
-> foreign key(detail_id) references author_detail(id)
-> );
ERROR 1215 (HY000): Cannot add foreign key constraint
二、正确案例
1.创建两个关联表(author)与被关联表(author_detail)
1.先创建被关联的表（从表）（author_detail）,可以创建成功
mysql> create table author_detail(
-> id int primary key auto_increment,#被关联表设置唯一约束，为主键
-> info varchar(256),
-> address varchar(256)
-> );
Query OK, 0 rows affected (0.43 sec)
2.再创建关联表（主表）（author），可以创建成功
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(64) not null,
-> mobile char(11) unique not null,
-> sex enum('男', '女') default '男',
-> age int default 0,
-> detail_id int unique not null,# 外键字段，设了唯一性，因为是一对一的表关系
-> foreign key(detail_id) references author_detail(id)
-> );
Query OK, 0 rows affected (0.63 sec)
2. 对两个表进行数据插入
# 先插入关联表(主表author)如数据出错
1.插入数据，出现错误，要先插入被关联表的数据
mysql>insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
ERROR 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`))
## 先插入被关联(author_detail)的表的数据，不会出错
1.给被关联的从表(author_detail)插入数据
mysql>insert into author_detail(info,address)values('Tom_info','Tom_address');
Query OK, 1 row affected (0.13 sec)
mysql> insert into author_detail(info,address)values('Bob_info','Bob_address');
Query OK, 1 row affected (0.13 sec)
mysql> insert into author_detail(info,address)values('Tom_info_sup','Tom_address_sup');
Query OK, 1 row affected (0.12 sec)
2.再插入关联表(主表author)的数据，不会出错
mysql>insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
Query OK, 1 row affected (0.13 sec)
mysql>insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
Query OK, 1 row affected (0.12 sec)
# cmd图例
mysql> select * from author_detail;
+----+--------------+-----------------+
| id | info | address |
+----+--------------+-----------------+
| 1 | Tom_info | Tom_address |
| 2 | Bob_info | Bob_address |
| 3 | Tom_info_sup | Tom_address_sup |
+----+--------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from author;
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 1 |
| 2 | Bob | 15666882233 | 男 | 0 | 2 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
3.修改关联表(主表author)
mysql>:update author set detail_id=3 where detail_id=2; #有没有被其他数据关联的数据，就可以修改
## 图示例
mysql> select * from author;
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 1 |
| 2 | Bob | 15666882233 | 男 | 0 | 3 | # 关联表的detail已经修改了
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
4.修改被关联表(从表author_detail)
mysql> update author_detail set id=10 where id=1;# 无法修改的，原因会在后面级联提到
ERROR 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.删除被关联表中记录
mysql> delete from author_detail where id=1; # 无法删除的
ERROR 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`))
重点：在对于表设置外键且没有级联关系的情况下
- 表的增加操作：先增加被关联表记录，再增加关联表记录
- 表的删除操作：先删除关联表记录，再删除被关联表记录
- 表的更新操作：关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
总结：以上我们实现的是一对一的表关系，并且是没有设置级联的，从上面的的修改和删除的部分代码我们可以看到时无法对被关联的表进行修改和删除记录操作的，后面就会详细的来讲解级联关系的表
6.2.2 一对一表关系设置外键(有级联关系)
一、设置级联关系的外键语法
create table 关联表(主表)名(
字段1 数据类型[约束条件]
·
·
字段n 数据类型[约束条件]
foreign key(主表字段) references 被关联表名(被关联表主键字段)
on update cascade # 两个表其中一个表数据更新，另一个表也跟着更新
on delete cascade # 两个表其中以一个数据被删除，另一个也跟着删除
);
二、有级联关系（一对一）案例
我们依然使用上面的作者和作者详细信息的案例，并且在已知的创建表规则的条件下去完成
此处没有错误案例分析
1.先删除上面案列创建的表
mysql>drop table author;
mysql>drop table author_detail;
2.先创建被关联的表(从表author_detail)
mysql> create table author_detail(
-> id int primary key auto_increment,
-> info varchar(256),
-> address varchar(256)
-> );
Query OK, 0 rows affected (0.42 sec)
3.再创建关联表(主表author)
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(64) not null,
-> mobile char(11) unique not null,
-> sex enum('男','女') default '男',
-> age int default 0,
-> detail_id int unique not null,
-> foreign key (detail_id) references author_detail(id)
-> on update cascade # 级联更新
-> on delete cascade # 级联删除
-> );
Query OK, 0 rows affected (0.42 sec)
# 插入表数据
# 必须先插入被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1); #错误
1.向被关联表author_detail插入数据
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
# cmd图示
mysql> select * from author_detail;
+----+----------+-------------+
| id | info | address |
+----+----------+-------------+
| 1 | Tom_info | Tom_address |
| 2 | Bob_info | Bob_address |
+----+----------+-------------+
2 rows in set (0.00 sec)
2.向关联表author插入数据
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
mysql> select * from author;
# cmd图示
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 1 |
| 2 | Bob | 15666882233 | 男 | 0 | 2 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
# 修改关联表
1.修改关联表auhtor数据
mysql> update author set detail_id=3 where detail_id=2; # 失败，被关联表里没有没有3对应的记录
mysql>: update author set detail_id=1 where detail_id=2; # 失败，1详情已被其他的作者关联
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
## cmd图示
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 1 |
| 2 | Bob | 15666882233 | 男 | 0 | 3 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
2.修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1; # 级联修改，同步关系关联表外键
## cmd图示
mysql> select * from author;
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 10 |
| 2 | Bob | 15666882233 | 男 | 0 | 3 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
mysql> select * from author_detail;
+----+--------------+-----------------+
| id | info | address |
+----+--------------+-----------------+
| 2 | Bob_info | Bob_address |
| 3 | Tom_info_sup | Tom_address_sup |
| 10 | Tom_info | Tom_address |
+----+--------------+-----------------+
3 rows in set (0.00 sec)
# 删除关联表author
mysql>: delete from author where detail_id=3; # 直接删除
# 删除被关联表 author_detail
mysql>: delete from author where detail_id=10; # 可以删除对被关联表author_detail无影响
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
mysql>: delete from author_detail where id=10;#可以删除，将关联表的记录对应的10作者详情级联删除
6.2.3 一对多表关系设置外键(有级联关系)
- 一对多的表关系，外键必须放在多的那一方，此时因为时一对多的关系，所以外键值不唯一
一、案例
以书和出版社来举例，一个书对应一个出版社，但是一个出版社可以出多本书（一对多的关系）
二、实例
此处按照正常的创建流程走，不在演示错误案例
# 出版社（publish）：id，name，address，phone
1.先创建多的一方，也就是被级联的表
mysql> create table publish(
-> id int primary key auto_increment,
-> name varchar(64),
-> address varchar(256),
-> phone char(20)
-> );
Query OK, 0 rows affected (0.39 sec)
# 书(book)：id，name，price，publish_id, author_id
2. 创建一的那一方，也就是关联表
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(64) not null,
-> price decimal(5, 2) default 0,
-> publish_id int, # 一对多的外键不能设置唯一
-> foreign key(publish_id) references publish(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.55 sec)
################ 对两个表插入数据
1.先增加被关联表(publish)的数据
mysql> insert into publish(name, address, phone) values
-> ('人民出版社', '北京', '010-1100'),
-> ('西交大出版社', '西安', '010-1190'),
-> ('中共教育出版社', '北京', '010-1200');
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
2.再增加关联表(book)的数据
mysql> insert into book(name, price, publish_id) values
-> ('西游记', 16.66, 1),
-> ('流浪记', 28.66, 1),
-> ('python从入门到放弃', 2.66, 2),
-> ('程序员修养之道', 43.66, 3),
-> ('好好活着', 18.88, 3);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
### cmd图示
mysql> select * from book;
+----+--------------------------+-------+------------+
| id | name | price | publish_id |
+----+--------------------------+-------+------------+
| 1 | 西游记 | 16.66 | 1 |
| 2 | 流浪记 | 28.66 | 1 |
| 3 | python从入门到放弃 | 2.66 | 2 |
| 4 | 程序员修养之道 | 43.66 | 3 |
| 5 | 好好活着 | 18.88 | 3 |
+----+--------------------------+-------+------------+
5 rows in set (0.00 sec)
mysql> select * from publish;
+----+-----------------------+---------+----------+
| id | name | address | phone |
+----+-----------------------+---------+----------+
| 1 | 人民出版社 | 北京 | 010-1100 |
| 2 | 西交大出版社 | 西安 | 010-1190 |
| 3 | 中共教育出版社 | 北京 | 010-1200 |
+----+-----------------------+---------+----------+
3 rows in set (0.00 sec)
3.没有被关联的字段，插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('流浪地球', 33.2, 4); # 失败
################ 更新操作
1.直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
###cmd图示
mysql> select * from book;
+----+--------------------------+-------+------------+
| id | name | price | publish_id |
+----+--------------------------+-------+------------+
| 1 | 西游记 | 16.66 | 10 |
| 2 | 流浪记 | 28.66 | 10 |
| 3 | python从入门到放弃 | 2.66 | 2 |
| 4 | 程序员修养之道 | 43.66 | 3 |
| 5 | 好好活着 | 18.88 | 3 |
+----+--------------------------+-------+------------+
5 rows in set (0.00 sec)
2.直接更新关联表的(book) 外键，修改的值对应被关联表(publish) 主键 如果存在，可以更新成功，反之失败
mysql>: update book set publish_id=2 where id=4; # 成功，此时被级联表的值是不受印象的
mysql>: update book set publish_id=1 where id=4; # 失败，因为外键字段没有这个值
############ 删除操作
1.删被关联表，关联表会被级联删除
mysql>: delete from publish where id = 2;
2.删关联表，被关联表不会发生变化
mysql>: delete from book where publish_id = 3;
# 假设：书与作者也是 一对多 关系，一个作者可以出版多本书
create table book(
id int primary key auto_increment,
name varchar(64) not null,
price decimal(5, 2) default 0,
publish_id int, # 一对多的外键不能设置唯一
foreign key(publish_id) references publish(id)
on update cascade
on delete cascade
# 建立与作者 一对多 的外键关联
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
6.2.4 多对多的表关系设置外键(有级联关系)
- 多对多的关系表，一定要创建第三张表来存储他们的关系，关系表中的每一个外键值不唯一
- 可以设置多个外键联合唯一
一、案例
此处以学生表和课程表为案例，完成 学生表 与 课程表 的 多对多 表关系的创建，并完成数据测试
- 学生表属性：sid(学生学号)，sname(学生姓名)，sage(学生年龄)
- 课程表属性：cid(课程号)，cname(课程名)
- 关系表属性：id，stu_id(学号), cus_id(课程号)
二、实例
##############创建表
1.创建被关联学生表student
create table student(
sid int primary key auto_increment,
sname char(8) not null,
sage int unsigned default 18
);
2.创建被关联课程表course
create table course(
cid int primary key auto_increment,
cname char(8) not null
);
3.创建学生和课程关系表
create table stu_cus(
id int primary key auto_increment,
stu_id int,
foreign key(stu_id) references student(sid)
on update cascade
on delete cascade,
cus_id int,
foreign key(cus_id) references course(cid)
on update cascade
on delete cascade,
unique(stu_id,cus_id)
);
##################插入表数据
1.student表添加数据
insert into student values(1,'xichen',18),(2,'chen',19),(3,'cecilia',20);
2.courset表添加数据
insert into course values(1,'python'),(2,'linux'),(3,'java'),(4,'go语言');
3.关系表stu_cus添加数据，必须在被关联的两张表已经有数据后再添加数据
insert into stu_cus values(1,1,1),(2,1,4),(3,2,1),(4,3,2),(5,3,4);
### cmd图示
mysql> select * from student;
+-----+---------+------+
| sid | sname | sage |
+-----+---------+------+
| 1 | xichen | 18 |
| 2 | chen | 19 |
| 3 | cecilia | 20 |
+-----+---------+------+
3 rows in set (0.00 sec)
mysql> select * from course;
+-----+----------+
| cid | cname |
+-----+----------+
| 1 | python |
| 2 | linux |
| 3 | java |
| 4 | go语言 |
+-----+----------+
4 rows in set (0.00 sec)
mysql> select * from stu_cus;
+----+--------+--------+
| id | stu_id | cus_id |
+----+--------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
| 5 | 3 | 4 |
+----+--------+--------+
5 rows in set (0.00 sec)
######################被关联表更新数据
1.向student学生表和course课程表添加数据不会影响关系表stu_cus
insert into student(sname,sage) values('xuchen',20);
insert into course(cname) values('c++');
####cmd测试
mysql> select * from stu_cus;
+----+--------+--------+
| id | stu_id | cus_id |
+----+--------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
| 5 | 3 | 4 |
+----+--------+--------+
5 rows in set (0.00 sec)
#########################修改关联表
1.修改student学生表和course课程表 会影响到关系表
update student set sid=5 where sid=3;# 如果修改student的id表里已存在，则不能修改
###cmd测试 关系表中原来stu_id为3的就都级联更新为5
mysql> select * from stu_cus;
+----+--------+--------+
| id | stu_id | cus_id |
+----+--------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 5 | 2 |
| 5 | 5 | 4 |
+----+--------+--------+
5 rows in set (0.00 sec)
########################删除关联表
1.删除student学生表和course课程表数据，关系表也会级联删除
delete from course where cid=1;
####cmd测试 关系表中原来cus_id为1的就都级联删除
mysql> select * from stu_cus;
+----+--------+--------+
| id | stu_id | cus_id |
+----+--------+--------+
| 2 | 1 | 4 |
| 4 | 5 | 2 |
| 5 | 5 | 4 |
+----+--------+--------+
3 rows in set (0.00 sec)
