mysql on cascade_MySQL外键约束_ON DELETE CASCADE/ON UPDATE CASCADE

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 intauto_increment primary key,-> name varchar(20))-> engine=InnoDB;

Query OK,0 rows affected (0.08sec)

mysql> insert into t_group values (1,'Group_1'),(2,'Group_2');

Query OK,2 rows affected (0.04sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select * fromt_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 nullprimary 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;

ERROR1451 (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;

ERROR1451 (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;

ERROR1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

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