MYSQL数据库（六）- 外键约束的参照操作

最新推荐文章于 2023-04-24 09:59:55 发布

人间四月天美丽春色

最新推荐文章于 2023-04-24 09:59:55 发布

阅读量603

点赞数

分类专栏： MySql

MySql 专栏收录该内容

43 篇文章 1 订阅

订阅专栏

一、外键引用约束的参照操作

其实可以简单理解为：在使用外键时候可以参照的依赖关系，例如父类删除子类也跟着删除。

二、4种外键约束

1、cascade:从父表删除或者更新且自动删除或更新子表中匹配的行
2、set null:从父表删除或更新行，并设置子表中的外键行为null,如果使用该选项，必须保证子表列没有指定not null
3、restrict：拒绝对父表的删除或更新操作。
4、no action:标准SQL的关键字，在mysql中于restrict相同

举例（前提：父列表已存在“sheng”）

mysql> create table sheng(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );

1、创建外键依赖的时候，我们指定一个参照，如下（on delete cascade）

cascade:从父表删除或者更新且自动删除或更新子表中匹配的行

mysql> create table city1(
    -> id smallint unsigned primary key auto_increment,
    -> usename varchar(20) not null,
    -> pid smallint unsigned,
    -> foreign key(pid) references sheng(id) on delete cascade);//这里添加了参照cascade
Query OK, 0 rows affected (0.17 sec)

2、查看创建好的数据表city1（show create table city1;）

mysql> show create table city1;
+-------
| Table | Create Table
---------------+
| city1 | CREATE TABLE `city1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `usename` varchar(20) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `city1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `sheng` 
(`id`) ON DEL
ETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------

添加参照成功

3、父列表中插入三条数据，验证插入是否成功。

这里注意：如果在两张表中插入数据，必须先在父表中插入记录，然后才能在子表中插入数据。原因很简单，因为子表是参照父表中的信息，如果父表中不存在这个信息，自表中也无法参照。

mysql> insert sheng(pname) values(‘A’);
Query OK, 1 row affected (0.08 sec)

mysql> insert sheng(pname) values(‘B’);
Query OK, 1 row affected (0.09 sec)

mysql> insert sheng(pname) values(‘C’);
Query OK, 1 row affected (0.09 sec)

mysql> select * from sheng;
+—-+——-+
| id | pname |
+—-+——-+
| 1 | A |
| 2 | B |
| 3 | C |
+—-+——-+
3 rows in set (0.03 sec)

插入成功，如下图：

这里写图片描述

4、在子列表中插入数据，注意插入的时候所参照的id值，父列表中一定要存在

第一次插入成功：
mysql> insert city1(usename,pid) values('tom',3);
Query OK, 1 row affected (0.06 sec)


第二次给pid插入一个父表中不存在的id，这时插入失败。所以添加外键约束的时候，一定要注意父列表中的参照列是否存在

mysql> insert city1(usename,pid) values('zangsan',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign 

key constraint f
ails (`t1`.`city1`, CONSTRAINT `city1_ibfk_1` FOREIGN KEY (`pid`) 

REFERENCES `sh
eng` (`id`) ON DELETE CASCADE)

第三次和第四次插入成功

mysql> insert city1(usename,pid) values('wangwu',1);
Query OK, 1 row affected (0.03 sec)

mysql> insert city1(usename,pid) values('wangwu',2);
Query OK, 1 row affected (0.06 sec)


----------------------------验证插入情况----------------------------------


mysql> select * from city1;
+----+---------+------+
| id | usename | pid  |
+----+---------+------+
|  1 | tom     |    3 |
|  3 | wangwu  |    1 |
|  4 | wangwu  |    2 |
+----+---------+------+
3 rows in set (0.00 sec)

这里写图片描述

注意：这里会被问，为什么id的编号是1、3、4而不是1、2、3 ？注意我们这里一共插入了4次，只不过第二次插入的时候我们故意输入错误，虽然插入失败，但是id的编号2已经被记录，只不过没有数据显示，再输入的参数id编号就是3。

5、进行父列表删除，查看子列表是否删除成功



-------------------------------下面进行删除------------------------------------

如果我们删除父列表中的id为3的参数，如图03，,04。

mysql> delete from sheng where id = 3;
Query OK, 1 row affected (0.08 sec)


验证一下我们父列表中是否删除成功

mysql> select * from sheng;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

再验证一下我们子列表中是否删除成功

mysql> select * from city1;
+----+---------+------+
| id | usename | pid  |
+----+---------+------+
|  3 | wangwu  |    1 |
|  4 | wangwu  |    2 |
+----+---------+------+
2 rows in set (0.00 sec)