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也同时被修改