No se puede eliminar o actualizar una fila padre: falla una restricción de clave foránea - MYSQL

Estoy recibiendo este error al intentar eliminar un usuario de la base de datos, sé que está haciendo esto porque el usuario que estoy tratando de eliminar es una clave externa en la tabla de citas, pero no sé cómo corregirlo o dónde me he equivocado. No se si cambia algo pero por si acaso he creado las tablas usando laravel

Tabla de usuarios

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;

Tabla de citas

 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`);
Shadow
Pregunta editada 19º abril 2017 в 11:14
Programación
mysql
19º abril 2017 в 11:02
No hay vistas aún
Solución

Este error se produce porque el usuario que desea eliminar tiene registros asociados en la tabla "citas". Tiene 2 opciones:

  1. Eliminar primero los registros asociados de la tabla de citas con una sentencia delete separada.

    1. Añada la opción on delete cascade a la clave extranjera appointments_user_id_foreign. Esta opción eliminará automáticamente cualquier registro asociado de la tabla appointments para el usuario a eliminar cuando elimine el registro del usuario.

La sentencia fk modificada tiene el siguiente aspecto:

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

La solución propuesta por @Nebster técnicamente elimina el mensaje de error, pero también permite tener registros huérfanos dentro de la tabla appointments - citas relacionadas con usuarios eliminados. Por lo tanto, la eliminación de la clave externa no es una opción sensata en mi opinión.

Shadow
Respuesta editada 19º abril 2017 в 11:23
Comentarios (0)

Parece que su clave externa en la tabla Citas tiene la opción En eliminar: Restringir. Cambie la restricción appointments_user_id_foreign a On delete: Cascade y debería poder eliminar usuarios conservando la clave externa.

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;
Nebster
Respuesta editada 19º abril 2017 в 11:21
Comentarios (1)

No se puede simplemente eliminar un usuario de la tabla usuarios. Está siendo referenciado por la tabla citas como índice de clave foránea. Creo que es justo que cuando se elimina un usuario se eliminen todas sus referencias de otras tablas.

En tu caso, puedes mejorar el diseño de tu base de datos. Puede hacer que la columna de clave externa de la tabla de citas sea opcional. Así, si se elimina un usuario, el user_id del registro correspondiente se puede establecer como NULL. Pero no tiene sentido almacenar citas de usuarios que ya no existen en el sistema. Otra opción sería, antes de borrar el usuario, borrar todas sus referencias de la tabla de citas y después borrar la referencia de la tabla de usuarios.

Comentarios (0)