MySQL SQL：无法删除或更新父行：外键约束失败

SQL: Cannot delete or update parent row: a foreign key constraint fails

提问by atif

Whenever I try to delete a survey from table "survey" like this:

每当我尝试从“调查”表中删除调查时，如下所示：

DELETE FROM surveys WHERE survey_id = 77

DELETE FROM surveys WHERE survey_id = 77

It prompts me an error stated below:

它提示我如下错误：

#1451 - Cannot delete or update a parent row: a foreign key constraint fails ('user_surveys_archive', CONSTRAINT 'user_surveys_archive_ibfk_6' FOREIGN KEY ('user_access_level_id') REFERENCES 'user_surveys' ('user_access_level_id') ON DELETE NO ACTION )

#1451 - 无法删除或更新父行：外键约束失败（'user_surveys_archive'，CONSTRAINT 'user_surveys_archive_ibfk_6' FOREIGN KEY（'user_access_level_id'）参考'user_surveys'（'user_access_level_id_id'）

First thing: I do not have any such table with this name "user_surveys_archive_ibfk_6"

第一件事：我没有任何名称为“user_surveys_archive_ibfk_6”的表

2nd thing: There is no record of this survey in other tables.

第二件事：在其他表中没有此调查的记录。

Any idea on how can I delete this record of fix this issue?

关于如何删除此修复此问题的记录的任何想法？

Edit

编辑

This is the line I found when I export the table Constraints for table surveys

这是我在导出表格约束以进行表格调查时发现的行

ALTER TABLE `surveys`
ADD CONSTRAINT `surveys_ibfk_1` FOREIGN KEY (`survey_type_id`) REFERENCES `survey_types` (`survey_type_id`) ON DELETE CASCADE ON UPDATE NO ACTION;`

回答by spencer7593

You will need to first remove or update some rows in table user_surveys_archive.

您需要先删除或更新 table 中的一些行user_surveys_archive。

Those rows are related to rows in the user_surveystable.

这些行与user_surveys表中的行相关。

Likely, there's a foreign key constraint defined on table user_surveysthat references rows in surveysyou are attempting to delete.

很可能，表user_surveys上定义了一个外键约束，它引用了surveys您尝试删除的行。

(You'd need to check the foreign key definition, quickest way to get that is a

（您需要检查外键定义，获得它的最快方法是

SHOW CREATE TABLE user_surveys 

And look for REFERENCES surveys. (Likely, its a column named survey_id, but we're just guessing without looking at the definitions of the foreign key constraints.)

并寻找REFERENCES surveys。（很可能，它是一个名为 的列survey_id，但我们只是在不查看外键约束的定义的情况下进行猜测。）

To find the rows in user_surveys_archivethat are preventing the DELETE from happening...

要找到user_surveys_archive阻止 DELETE 发生的行...

SELECT a.*
  FROM user_surveys_archvive a
  JOIN user_surveys u
    ON u.user_access_level_id = a.user_access_level_id  
  JOIN surveys s
    ON s.survey_id = u.survey_id    -- change this to whatever the FK is
 WHERE s.survey_id = 77

It's likely that the foreign key constraint from user_surveysto surveysis defined with ON DELETE CASCADE. The attempt to delete rows from surveysidentifies rows in user_surveysthat should automatically be removed.

很可能来自user_surveysto的外键约束surveys是用ON DELETE CASCADE. 从surveys标识行中删除行的尝试user_surveys应自动删除。

The attempt to automatically remove the rows from user_surveysis what's violating the foreign key constraint defined in user_surveys_archive. And that foreign key is notdefined with ON DELETE CASCADE.

尝试自动从中删除行user_surveys违反了 中定义的外键约束user_surveys_archive。并且该外键不是用ON DELETE CASCADE.

(The other possibility is that there's a trigger defined that's doing some DML operations, but that would be odd.)

（另一种可能性是定义了一个触发器来执行一些 DML 操作，但这会很奇怪。）

---

---

Once you identify the rows, you need to decide what changes to make to allow you to remove rows from surveys.

确定行后，您需要决定进行哪些更改以允许您从surveys.

You can either DELETE the rows, or UPDATE them.

您可以删除行，也可以更新它们。

To delete the rows from user_surveys_archive, modify the query above and replace SELECTwith DELETE. If the user_access_level_idcolumn in user_surveys_archiveallows for NULL values, you can do an update.

要从 中删除行user_surveys_archive，请修改上面的查询并替换SELECT为DELETE。如果user_access_level_id列中user_surveys_archive允许 NULL 值，您可以进行更新。

Replace SELECT a.* FROMwith UPDATE, and add SET a.user_access_level_id = NULLon a line above the WHEREclause...

替换SELECT a.* FROM为UPDATE, 并SET a.user_access_level_id = NULL在WHERE子句上方添加一行...

UPDATE user_surveys_archvive a
  JOIN user_surveys u
    ON u.user_access_level_id = a.user_access_level_id  
  JOIN surveys s
    ON s.survey_id = u.survey_id    -- change this to whatever the FK is
   SET a.user_access_level_id = NULL
 WHERE s.survey_id = 77

---

---

(It seems strange to me that name of the foreign key column is user_access_level_id. But its just a column name, it could be named anything... seems odd to me because of the conventions and patterns that we follow in naming foreign key columns.)

（外键列的名称是 ，这对我来说似乎很奇怪user_access_level_id。但它只是一个列名，它可以被命名为任何东西......对我来说似乎很奇怪，因为我们在命名外键列时遵循的惯例和模式。）

回答by Marius Cucuruz

You should allow the foreign key to be NULLand then choose ON DELETE SET NULL.

您应该允许外键为NULL，然后选择ON DELETE SET NULL。

Personally I would recommend using both "ON UPDATE CASCADE" as well as "ON DELETE SET NULL" to avoid unnecessary complications, however your setup may dictate a different approach.

我个人建议同时使用“ON UPDATE CASCADE”和“ON DELETE SET NULL”以避免不必要的并发症，但是您的设置可能会规定不同的方法。

Hope this helps.

希望这可以帮助。

回答by JKirkbride

The survey_idof 77 must be referenced in a table possibly named user_surveys_archive. The error is saying the name of the foreign key restraint which is the user_surveys_archive_ibfk_6. If you need to delete survey_id = 77you will have to delete any child records or other records that reference it as a foreign key.

在survey_id77必须在可能命名的表被引用user_surveys_archive。错误是说外键限制的名称是user_surveys_archive_ibfk_6. 如果需要删除survey_id = 77，则必须删除任何子记录或将其作为外键引用的其他记录。

EditAfter posting my answer I saw the comments above. atif the foreign key in the user_surveys_archivetable may be named differently but still equal the value of 77 that you are trying to delete, and/or be referencing the survey_id. If that is not the case then there are some other problems within the database. You could try and look at the code for the FK to see how it was made and what fields it is referencing, or run a query to see if there are any records in the user_surveys_archivewhere the user_access_level_idis = 77.

编辑发布我的答案后，我看到了上面的评论。atifuser_surveys_archive表中的外键可能以不同的方式命名，但仍等于您尝试删除的值 77，和/或正在引用survey_id. 如果不是这种情况，那么数据库中还有其他一些问题。您可以尝试查看 FK 的代码以了解它是如何制作的以及它引用了哪些字段，或者运行查询以查看is = 77的user_surveys_archive位置是否有任何记录user_access_level_id。

Edit 2spencer7593's answer lays out how to run some of the queries mentioned in my answer.

编辑 2spencer7593 的答案列出了如何运行我的答案中提到的一些查询。