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

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

提问by TF120

I'm getting this error when trying to delete a user from the database, I know it's doing this because the user I'm trying to delete is a foreign key in the appointments table, but I don't know how to correct it or where I have gone wrong. Not sure if it changes anything but just incase I created the tables using laravel

尝试从数据库中删除用户时出现此错误，我知道这样做是因为我尝试删除的用户是约会表中的外键，但我不知道如何更正它或我哪里出错了。不确定它是否会改变任何东西，但只是以防万一我使用 laravel 创建了表格

Users table

用户表

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `firstname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `surname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `postcode` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dateofbirth` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` tinyint(4) NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Appointments table

约会表

 CREATE TABLE `appointments` (
      `id` int(10) UNSIGNED NOT NULL,
      `time` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
      `date` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      `doctor_id` int(10) UNSIGNED NOT NULL,
      `user_id` int(10) UNSIGNED NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    ALTER TABLE `appointments`
      ADD PRIMARY KEY (`id`),
      ADD KEY `appointments_doctor_id_foreign` (`doctor_id`),
      ADD KEY `appointments_user_id_foreign` (`user_id`);

    ALTER TABLE `appointments`
      ADD CONSTRAINT `appointments_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`),
      ADD CONSTRAINT `appointments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

回答by Shadow

You get this error because the user you would like to delete has associated records within the appointmentstable. You have 2 options:

您收到此错误是因为您要删除的用户在appointments表中具有关联的记录。您有 2 个选择：

1. Delete the associated records from the appointments table first with a separate deletestatement.

2. Add on delete cascadeoption to appointments_user_id_foreignforeign key. This option will automatically remove any associated records from the appointmentstable for the user to be deleted when you delete the user's record.

1. 首先使用单独的delete语句从约会表中删除关联的记录。

2. 添加删除级联选项到appointments_user_id_foreign外键。appointments当您删除用户的记录时，此选项将自动从表中删除任何关联的记录以供删除用户。

The modified fk statement looks like as follows:

修改后的 fk 语句如下所示：

... ADD CONSTRAINT `appointments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

The solution proposed by @Nebster technically removes the error message, but also enables having orphan records within the appointmentstable - appointments related to deleted users. Therefore, removing the foreign key is not a sensible option in my opinion.

@Nebster 提出的解决方案在技术上删除了错误消息，但也启用了appointments表中的孤立记录- 与已删除用户相关的约会。因此，在我看来，删除外键不是一个明智的选择。

回答by Ajay

SET FOREIGN_KEY_CHECKS=0;– to disable them

SET FOREIGN_KEY_CHECKS=0;– 禁用它们

SET FOREIGN_KEY_CHECKS=1;– to re-enable them

SET FOREIGN_KEY_CHECKS=1;– 重新启用它们

回答by Nebster

Seems like your Foreign key in Appointments table has On delete: Restrictoption. Change the Constraint appointments_user_id_foreignto On delete: Cascadeand you should be able to delete Users while preserving Foreign key.

似乎您在约会表中的外键具有删除时：限制选项。将约束约会_user_id_foreign 更改为On delete: Cascade，您应该能够在保留外键的同时删除用户。

ALTER TABLE "appointments" DROP FOREIGN KEY "appointments_user_id_foreign";

ALTER TABLE "appointments" ADD CONSTRAINT "appointments_user_id_foreign" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE;

回答by Maimoona Iqbal

You cannot simply delete a user from table users. It is being referenced by the appointments table as foreign key index. I think it is fair enough that when a user is deleted all his references should be deleted from other tables.

您不能简单地从表 users 中删除用户。它被约会表引用为外键索引。我认为当一个用户被删除时，他的所有引用都应该从其他表中删除是很公平的。

For your scenario, you can improve your database design. You can make the foreign key column in appointments table optional. So if user is deleted the corresponding record's user_id can be set as NULL. But it is meaningless to store appointments for those users who no longer exist in the system. Another approach be to before deleting that user, delete all his corresponding references from appointments table and then delete the reference from users table

对于您的场景，您可以改进您的数据库设计。您可以将约会表中的外键列设为可选。所以如果用户被删除，相应记录的user_id 可以设置为NULL。但是为那些不再存在于系统中的用户存储约会是没有意义的。另一种方法是在删除该用户之前，从约会表中删除所有对应的引用，然后从用户表中删除引用

回答by Moh .S

If you want a temporary solution, then try the following

如果您想要临时解决方案，请尝试以下操作

SET FOREIGN_KEY_CHECKS=OFF; -- to disable foreign key checks
//drop the table and then
SET FOREIGN_KEY_CHECKS=ON; -- to re-enable foreign key checks

回答by Kishan Solanki

When you're deleting from phpMyAdmin, just uncheckenable foreign key checksat bottom

当您从 phpMyAdmin 中删除时，只需enable foreign key checks在底部取消选中

回答by Orhan

There are a couple of options, but in the end there are really only two main options:

有几个选项，但最终只有两个主要选项：

1. Add on delete cascademeaning that when user is deleted appointments relating to that user should also be deleted as suggested by Shadow(point number two)
2. Add on delete set nullmeaning that when user is deleted appointments user_id relating to that user should be set to null (though you will have to change user_id int(10) UNSIGNED NOT NULLto user_id int(10) UNSIGNED DEFAULT NULL

1. 添加on delete cascade意味着当用户被删除时，与该用户相关的约会也应该按照Shadow 的建议删除（第二点）
2. 添加on delete set null意味着当用户被删除时，与该用户相关的约会 user_id 应设置为 null（尽管您必须更改user_id int(10) UNSIGNED NOT NULL为user_id int(10) UNSIGNED DEFAULT NULL

Which solution should one choose?

应该选择哪种解决方案？

Lets say this is for a clinic and the clinic gets a request from a user to delete him from the db. That is fine, happens all the time. But the clinic still wants to keep history of all the appointments in the db. In this case you would go with solution number 2. In this way when user is deleted you will still have the appointment in the db, but the user_id will be null as that user does not exist anymore. If the clinic does not care about history of the appointments then you can go with solution number one. Though one can also set user fields to null instead of deleting the row completely, and in that case you would go with solution number one as one would never actually need to fully delete a user record.

假设这是一个诊所，诊所收到用户的请求，要求将他从数据库中删除。那很好，一直发生。但是诊所仍然希望保留数据库中所有约会的历史记录。在这种情况下，您将使用解决方案编号 2。这样，当用户被删除时，您仍将在数据库中拥有约会，但 user_id 将为空，因为该用户不再存在。如果诊所不关心约会的历史，那么您可以使用第一解决方案。虽然也可以将用户字段设置为 null 而不是完全删除行，在这种情况下，您将使用第一号解决方案，因为实际上永远不需要完全删除用户记录。