MYSQL数据库(六)- 外键约束的参照操作
一、外键引用约束的参照操作
其实可以简单理解为:在使用外键时候可以参照的依赖关系,例如父类删除子类也跟着删除。
二、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)
如上图所示,父列表中id为3的删除后,子列表中的也会被删除。这就是上面所说的,给外键约束添加参照后,父列表操作会关联到子列表一起实现删除或者更改