MySQL外键约束_ON DELETE CASCADE/ON UPDATE CASCADE

时间:2022-04-22 20:14:58

MySQL通过外键约束实现数据库的参照完整性,外键约束条件可在创建外键时指定,table的存储引擎只能是InnoDB,因为只有这种存储模式才支持外键。

外键约束条件有以下4种:

(1)restrict方式:同no action,都是立即检查外键约束;

         - - 限制,指的是如果子表引用父表的某个字段的值,那么不允许直接删除父表的该值。

 

(2)cascade方式:在父表上update/delete记录时,同步update/delete子表的匹配记录 ;

         On delete cascade从mysql3.23.50开始可用,on update cascade从mysql4.0.8开始可用 ;

         -- 级联,删除/更新父表的某条记录,子表中引用该值的记录会自动被删除/更新。

 

(3)No action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 这个是ANSI SQL-92标准,从mysql4.0.8开始支持;

         --无参照完整性关系,有了也不生效。

 

(4)set null方式:在父表上update/delete记录时,将子表上匹配记录的列设为null 要注意子表的外键列不能为not null 

         On delete set null从mysql3.23.50开始可用;,on update set null从mysql4.0.8开始可用 。

 

首先创建一个用户表,并插入两条记录:

mysql> create table t_group
    -> (id int auto_increment primary key,
    -> name varchar(20))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_group values (1,'Group_1'),(2,'Group_2');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_group;
+----+---------+
| id | name    |
+----+---------+
|  1 | Group_1 |
|  2 | Group_2 |
+----+---------+
2 rows in set (0.00 sec)

 

1.级联方式

创建级联子表:

mysql> create table t_user
    -> (id int not null primary key,
    -> name varchar(10),groupid int,
    -> foreign key(groupid) references t_group(id)
    -> on delete cascade on update cascade)
    -> engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

完整性约束测试,以下例子都省略完整性测试:

mysql> insert into t_user values (1,'dayu',1),(2,'duoduo',2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t_user values (1,'huanhuan',1);   //实体完整性测试,不能插入重复主键值
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t_user values (3,'maiqi',3);      //参照完整性测试,不能插入在主表中不存在的外键值
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_user`, CONSTRAINT `t_user_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `t_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

级联测试:

mysql> select * from t_user;
+----+--------+---------+
| id | name   | groupid |
+----+--------+---------+
|  1 | dayu   |       1 |
|  2 | duoduo |       2 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> update t_group set id=3 where id=2;  //从主表中更新一个记录
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_user;  /改变主表数据,从表数据随着更新
+----+--------+---------+
| id | name   | groupid |
+----+--------+---------+
|  1 | dayu   |       1 |
|  2 | duoduo |       3 |
+----+--------+---------+
2 rows in set (0.00 sec)

  

mysql> select * from t_user;
+----+--------+---------+
| id | name   | groupid |
+----+--------+---------+
|  1 | dayu   |       1 |
|  2 | duoduo |       3 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> delete from t_group where id=3;  //从主表中删除一个记录
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_user;   //子表中对应的记录自动被删除
+----+------+---------+
| id | name | groupid |
+----+------+---------+
|  1 | dayu |       1 |
+----+------+---------+
1 row in set (0.00 sec)

  

2.置空模式(set null,主表记录被删除,从表中对应的值设置为NULL)

首先创建一个子表:

 mysql> select * from t_group;
 +----+---------+
 | id | name |
 +----+---------+
 | 1 | Group_1 |
 | 2 | Group_2 |
 +----+---------+
 2 rows in set (0.00 sec)

mysql> create table t_user_1
    -> (id int not null primary key,
    -> name char(10),groupid int,
    -> foreign key(groupid) references t_group(id)
    -> on delete set null on update set null)
    -> engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t_user_1 values (1,'dayu',1),(2,'duoduo',2),(3,'huanhuan',2),(4,'maiqi',1);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t_user_1;
+----+----------+---------+
| id | name     | groupid |
+----+----------+---------+
|  1 | dayu     |       1 |
|  2 | duoduo   |       2 |
|  3 | huanhuan |       2 |
|  4 | maiqi    |       1 |
+----+----------+---------+
4 rows in set (0.00 sec)
mysql> update t_group set id=3 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_user_1;
+----+----------+---------+
| id | name     | groupid |
+----+----------+---------+
|  1 | dayu     |    NULL |
|  2 | duoduo   |       2 |
|  3 | huanhuan |       2 |
|  4 | maiqi    |    NULL |
+----+----------+---------+
4 rows in set (0.00 sec)

mysql> delete from t_group where id=2;   //从主表中删除一个记录
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user_1;  //子表中对应的属性值被自动设置为NULL
+----+----------+---------+
| id | name     | groupid |
+----+----------+---------+
|  1 | dayu     |    NULL |
|  2 | duoduo   |    NULL |
|  3 | huanhuan |    NULL |
|  4 | maiqi    |    NULL |
+----+----------+---------+
4 rows in set (0.00 sec)

 

3.禁止模式(no action/restrict),如果在子表中有引用,则不允许在主表中进行更新或删除

首先创建一个子表:

mysql> select * from t_group;
 +----+---------+
 | id | name |
 +----+---------+
 | 1 | Group_1 |
 | 2 | Group_2 |
 +----+---------+
 2 rows in set (0.00 sec)

mysql> create table t_user_2
    -> (id int not null primary key,
    -> name char(10),groupid int,
    -> foreign key(groupid) references t_group(id)
    -> on delete no action on update restrict)
    -> engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t_user_2 values (1,'dayu',1),(2,'duoduo',2),(3,'huanhuan',2),(4,'maiqi',1);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t_user_2;
+----+----------+---------+
| id | name     | groupid |
+----+----------+---------+
|  1 | dayu     |       1 |
|  2 | duoduo   |       2 |
|  3 | huanhuan |       2 |
|  4 | maiqi    |       1 |
+----+----------+---------+
4 rows in set (0.00 sec)
mysql> update t_group set id=3 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_user_2`, CONSTRAINT `t_user_2_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `t_group` (`id`) ON DELETE NO ACTION)
mysql> delete from t_group where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_user_2`, CONSTRAINT `t_user_2_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `t_group` (`id`) ON DELETE NO ACTION)

 

4.主从表的删除

删除从表没有限制,如下:

mysql> drop table t_user_2;
Query OK, 0 rows affected (0.01 sec)

如果存在引用的从表,则主要不能随意删除:

mysql> drop table t_group;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

 

经过以上测试,了解到MySQL通过外键约束实现了数据的完整性与一致性。