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

Question

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

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`);

Answer 1

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

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

2. Add on delete cascade option to appointments_user_id_foreign foreign key. This option will automatically remove any associated records from the appointments table for the user to be deleted when you delete the user's record.

The modified fk statement looks like as follows:

... 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 appointments table - appointments related to deleted users. Therefore, removing the foreign key is not a sensible option in my opinion.

Answer 2

SET FOREIGN_KEY_CHECKS=0; – to disable them

SET FOREIGN_KEY_CHECKS=1; – to re-enable them