删除 MySQL 中的父表
我创建了两个简单的表格来说明这个问题。
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`father_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_22B354292055B9A2` (`father_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TABLE `father` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
ALTER TABLE `child`
ADD CONSTRAINT `child_ibfk_1` FOREIGN KEY (`father_id`) REFERENCES `father` (`id`)
ON DELETE CASCADE;
想象一下，子表中有一些行通过外键链接到父表。
如果我删除父表中的一行，而该行在子表中具有链接到它的行，则无论如何我都可以删除它，因为我激活了“ON DELETE CASCADE”。
问题是，如果我尝试删除父表，即使父表和子表上都没有行，我也会收到以下错误消息（在 phpmyadmin 中）：
#1217 - Cannot delete or update a parent row: a foreign key constraint fails
我尝试了此命令：
SET foreign_key_checks = 0;
但同样的错误出现消息。
我可以删除整个数据库并再次创建它，但如果有其他解决方案（例如行的 ON DELETE CASCADE），我不想这样做。
有什么建议吗？
I've created two simple tables to ilustrate this question.
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`father_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_22B354292055B9A2` (`father_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TABLE `father` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
ALTER TABLE `child`
ADD CONSTRAINT `child_ibfk_1` FOREIGN KEY (`father_id`) REFERENCES `father` (`id`)
ON DELETE CASCADE;
Imagine that there are rows in the child table linked to the father table through the foreign key.
If I delete a row in the father table that has rows linked to it in the child table, I can delete it anyway because I have "ON DELETE CASCADE" activated.
The problem is that if I try to drop the father table, even if there are no rows neither on the father table nor on the child table, I get the following error message (in phpmyadmin):
#1217 - Cannot delete or update a parent row: a foreign key constraint fails
I tried this command:
SET foreign_key_checks = 0;
But the same error message appears.
I could delete the whole database and create it again, but I don't want to do that if there is another solution (like ON DELETE CASCADE for the rows).
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试删除约束本身。更改表子级删除外键child_ibfk_1；
Try dropping the constraint itself. alter table child drop foreign key child_ibfk_1;