欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MYSQL数据库(六)- 外键约束的参照操作

程序员文章站 2024-03-13 11:43:45
...

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

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

二、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)

插入成功,如下图:

MYSQL数据库(六)- 外键约束的参照操作


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)

MYSQL数据库(六)- 外键约束的参照操作

注意:这里会被问,为什么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)

MYSQL数据库(六)- 外键约束的参照操作

如上图所示,父列表中id为3的删除后,子列表中的也会被删除。这就是上面所说的,给外键约束添加参照后,父列表操作会关联到子列表一起实现删除或者更改