MySQL：无法删除或更新父行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25697157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Cannot delete or update a parent row: a foreign key constraint fails
提问by Aleksa
Hi i am trying to make simple database with 2 tables, first for user information, and second for their uploads, because it's project for faculty, i have some assignments... And one is to use foreign key.
嗨，我正在尝试使用 2 个表制作简单的数据库，第一个用于用户信息，第二个用于上传，因为它是教师项目，我有一些作业......其中一个是使用外键。
DROP TABLE IF EXISTS `korisnici`;
CREATE TABLE `korisnici` (
`UserID` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(12) NOT NULL,
`password` VARCHAR(32) NOT NULL,
`email` VARCHAR(32) NOT NULL,
`telefon` VARCHAR(16) NOT NULL,
PRIMARY KEY (`UserID`)
);
DROP TABLE IF EXISTS `slike`;
CREATE TABLE `slike` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(200) NOT NULL,
`size` INTEGER(11) NOT NULL,
`type` VARCHAR(200) NULL,
`file_path` VARCHAR(200) NOT NULL,
`username` VARCHAR(12) NOT NULL,
`naslov` VARCHAR(32) NOT NULL,
`adresa` VARCHAR(80) NOT NULL,
`opis` VARCHAR(1200) NOT NULL,
`datum` DATE NOT NULL,
`UserID` INTEGER(11) NOT NULL,
PRIMARY KEY (`id`)
);
ALTER TABLE `slike` ADD FOREIGN KEY (UserID) REFERENCES `korisnici` (`UserID`);
-- ALTER TABLE `korisnici` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `slike` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ---
-- Test Data
-- ---
INSERT INTO `korisnici` (`UserID`,`username`,`password`,`email`,`telefon`) VALUES('1','dd','d','d','d');
INSERT INTO `slike` (`id`,`name`,`size`,`type`,`file_path`,`username`,`naslov`,`adresa`,`opis`,`datum`,`UserID`) VALUES('2','a','122','png','ksks/sss','dsss','aaaa','sss','ssss','2014/09/04','2');
ERROR:
错误：
ERROR 1217 (23000) at line 14: Cannot delete or update a parent row: a foreign key constraint fails
Anybody now where is the probel and how could i fix it? It also doesn't work on sqlfiddle when i insert some values for testing. Thanks :)
任何人现在探针在哪里，我该如何修复它？当我插入一些用于测试的值时，它也不适用于 sqlfiddle。谢谢 ：）
回答by Benison Sam
You have child record and so since you have put the ON DELETE RESTRICTas well as ON UPDATE RESTRICTconstraints(I mean as they are default) whatever changes you make on the parent row i.e. a row in korisnicitable with child rows in sliketable will be restricted by MySQL.
Now for deletion you can do something like this:
你有孩子记录等，因为你已经把ON DELETE RESTRICT和ON UPDATE RESTRICT约束（我的意思是，他们是默认的），你就父行即行中的任何改变korisnici表与子行S形表将受MySQL限制。
现在要删除，您可以执行以下操作：
DELETE FROM `slike` WHERE `UserID`=`<UserId you want to delete>`;
DELETE FROM `korisnici` WHERE `UserID`=`<UserId you want to delete>`;
And for Updation...
DELETE FROM `slike` WHERE `UserID`=`<UserId you want to delete>`;
DELETE FROM `korisnici` WHERE `UserID`=`<UserId you want to delete>`;
而对于更新...
Anyways the better option always is to mention the appropriate foeign constraints while specifying or establishing the foreign key.
To get bit more info you can refer this link
无论如何，更好的选择总是在指定或建立外键时提及适当的外来约束。
要获得更多信息，您可以参考此链接
回答by Jens Krogsboell
I think the error message is actually misleading. What I see from your code is that it is the insert into SLIKE that fails because UserID=2 does not match the UserID of the previous insert into KORISNICI.
我认为错误消息实际上具有误导性。我从您的代码中看到的是，插入 SLIKE 失败了，因为 UserID=2 与之前插入 KORISNICI 的 UserID 不匹配。
回答by Marius Cucuruz
There's really no point to choose ON DELETE CASCADE. So the alternative would be to allow the foreign key to be NULLand then choose ON DELETE SET NULL.
选择ON DELETE CASCADE真的没有意义。因此，替代方法是允许外键为NULL，然后选择ON DELETE SET NULL。
Personally I would use "ON UPDATE CASCADE" pared with "ON DELETE SET NULL" to avoid unnecessary complications, but on your set up you may want a different approach.
我个人会使用“ ON UPDATE CASCADE”与“ ON DELETE SET NULL”相提并论，以避免不必要的并发症，但在您的设置中，您可能需要不同的方法。
Hope this helps.
希望这可以帮助。
