主外键的表删除数据
CREATE TABLE `p1` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `f1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `f1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
mysql> insert into p1 values (1,'bayue');
Query OK, 1 row affected (0.03 sec)
mysql> insert into f1 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from p1,f1;
+----+-------+----+------+
| id | name | id | pid |
+----+-------+----+------+
| 1 | bayue | 1 | 1 |
+----+-------+----+------+
1 row in set (0.00 sec)
mysql> delete p1,f1 from p1,f1 where p1.id=f1.pid;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ran`.`f1`, CONSTRAINT `f1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))
mysql> explain delete p1,f1 from p1,f1 where p1.id=f1.pid;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| 1 | SIMPLE | p1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | f1 | ref | pid | pid | 5 | ran.p1.id | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
2 rows in set (0.00 sec)
mysql> delete p1,f1 from f1,p1 where p1.id=f1.pid;
Query OK, 2 rows affected (0.01 sec)
mysql> explain delete f1,p1 from p1,f1 where p1.id=f1.pid;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| 1 | SIMPLE | p1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | f1 | ref | pid | pid | 5 | ran.p1.id | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
2 rows in set (0.00 sec)
主外键表删除数据演示
最新推荐文章于 2024-09-14 10:05:46 发布