mysql中利用外键实现级联删除，更新

MySQL中利用外键实现级联删除、更新

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、CASCADE和SET NULL

其中：

RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；

CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；

SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

因为只有InnoDB引擎才允许使用外键，所以，我们的数据表必须使用InnoDB引擎。

举例：

创建表：user，userinfo两个表

CREATE TABLE user (
user_id int(4) primary key NOT NULL AUTO_INCREMENT,

 role_name varchar(20),
 login_name varchar(20),
 login_pwd varchar(20),

user_name varchar(20),

 creat_time date

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE userinfo (
sn int(4)  primary key NOT NULL AUTO_INCREMENT,
user_id int(4) NOT NULL,
info varchar(20) DEFAULT NULL,
CONSTRAINT userinfo_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表结构信息：mysql>   desc user;

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| user_id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| role_name  | varchar(20) | YES  |     | NULL    |                |
| login_name | varchar(20) | YES  |     | NULL    |                |
| login_pwd  | varchar(20) | YES  |     | NULL    |                |
| user_name  | varchar(20) | YES  |     | NULL    |                |
| creat_time | date        | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

插入数据：

mysql>  insert into user(role_name,login_name) values('taylor','caotingli');

mysql>  insert into user(role_name,login_name) values('taylor','yang');

mysql>  insert into userinfo(user_id,info) values(1,'333333');
mysql>  insert into userinfo(user_id,info) values(1,'111111111');
mysql>  insert into userinfo(user_id,info) values(2,'22222');

查询数据：

mysql> select * from t_user;
+---------+-----------+------------+-----------+-----------+------------+
| user_id | role_name | login_name | login_pwd | user_name | creat_time |
+---------+-----------+------------+-----------+-----------+------------+
|       1 | taylor    | caotingli  | NULL      | NULL      | NULL       |
|       2 | taylor    | yang       | NULL      | NULL      | NULL       |
+---------+-----------+------------+-----------+-----------+------------+
2 rows in set (0.00 sec)


mysql> select * from userinfo;
+----+---------+-----------+
| sn | user_id | info      |
+----+---------+-----------+
|  1 |       1 | 333333    |
|  2 |       1 | 111111111 |
|  3 |       2 | 22222     |
+----+---------+-----------+

验证删除功能：

mysql> delete from user where user_id=1;
Query OK, 1 row affected (0.06 sec)

mysql> select * from user;
+---------+-----------+------------+-----------+-----------+------------+
| user_id | role_name | login_name | login_pwd | user_name | creat_time |
+---------+-----------+------------+-----------+-----------+------------+
|       2 | taylor    | yang       | NULL      | NULL      | NULL       |
+---------+-----------+------------+-----------+-----------+------------+
1 row in set (0.00 sec)

mysql> select * from userinfo;
+----+---------+-------+
| sn | user_id | info  |
+----+---------+-------+
|  3 |       2 | 22222 |
+----+---------+-------+
1 row in set (0.00 sec)

注意：在删除了user表中id=1的数据时，表userinfo中对应的user_id=1的数据也删除

验证更新数据功能：

mysql> update user set user_id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from user;
+---------+-----------+------------+-----------+-----------+------------+
| user_id | role_name | login_name | login_pwd | user_name | creat_time |
+---------+-----------+------------+-----------+-----------+------------+
|       1 | taylor    | yang       | NULL      | NULL      | NULL       |
+---------+-----------+------------+-----------+-----------+------------+
1 row in set (0.00 sec)


mysql> select * from userinfo;
+----+---------+-------+
| sn | user_id | info  |
+----+---------+-------+
|  3 |       1 | 22222 |
+----+---------+-------+
1 row in set (0.00 sec)

注意：此时修改了user表中的user_id时，对应的userinfo中的user_id也同时被修改