Problem mit Foreign Key Constraints

JR Cologne

Well-Known Member
c-b Experte
#1
Schönen guten Abend zusammen,

ich benötige mal Hilfe in Sachen SQL und Datenbanken, genauer gesagt Foreign Key Constraints.

Vorliegend ist folgendes DB-Schema:

SQL:
CREATE DATABASE IF NOT EXISTS `retask`;
USE `retask`;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_resets_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `task_list_id` int(10) unsigned DEFAULT NULL,
  `task` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tasks_user_id_index` (`user_id`),
  KEY `tasks_task_list_id_index` (`task_list_id`),
  CONSTRAINT `tasks_task_list_id_foreign` FOREIGN KEY (`task_list_id`) REFERENCES `task_lists` (`id`),
  CONSTRAINT `tasks_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `task_lists` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `task_lists_user_id_index` (`user_id`),
  CONSTRAINT `task_lists_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_name_unique` (`name`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Wie ihr seht habe ich also User, Tasks und Task Lists. Ein User hat mehrere Tasks und Task Lists. Eine Task bzw. Task List gehört immer nur zu einem User.
Eine Task List hat mehrere Tasks, während eine Task folglich nur einer Task List angehört. Eine Task kann allerdings auch keiner Task List angehören.

Ich hoffe, das ist soweit verständlich.

Wenn ich nun versuche, einen User zu löschen, bekomme ich folgende Fehlermeldung. Voraussetzung hierfür ist, dass der User eine Task hat, die zu einer Task List gehört.

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`retask`.`tasks`, CONSTRAINT `tasks_task_list_id_foreign` FOREIGN KEY (`task_list_id`) REFERENCES `task_lists` (`id`)) (SQL: delete from `users` where `id` = 1)

Wenn ich mein DB-Schema richtig verstehe, habe ich die Foreign Key Constraints doch folgendermaßen eingerichtet:
Wird ein User gelöscht, werden sowohl seine Tasks als auch seine Task Lists gelöscht. Warum meckert der dann, wenn ich den User lösche, welcher eine Task mit einer zugehörigen Task List besitzt?

Habe ich irgendeinen Fehler in der Beziehung oder verstehe ich den Sinn von Foreign Key Constraints einfach nicht richtig?

Wäre super, wenn mir jemand auf die Sprünge helfen könnte. Mache mit Sicherheit irgendeinen Denkfehler...

Schon mal vielen Dank! :)

Viele Grüße,
JR
 

Jan Krüger

Well-Known Member
c-b Team
c-b Experte
#2
Lies den Fehler mal genauer... er bezieht sich auf diesen Constraint hier:
Code:
CONSTRAINT `tasks_task_list_id_foreign` FOREIGN KEY (`task_list_id`) REFERENCES `task_lists` (`id`),
- und der hat kein Cascade. Beim Löschen des Users läuft der Cascade also mutmaßlich zuerst in Richtung task_lists. Die tasks, die darauf referenzieren, existieren aber noch, also bricht es wegen dem Constraint ab - auch wenn die tasks später sowieso noch durch den zweiten Cascade (tasks.user_id <- users.id) gelöscht würden.
Die "Lösung" ist also, diesem Constraint auch noch ein "on delete cascade" zu verpassen... auch wenn das den Nachteil hat, dass man dadurch dann beim Löschen einer Liste auch leicht ungewollt alle Tasks mitlöschen könnte. Ich glaube, in MySQL und MariaDB gibt es keine andere Möglichkeit. In PostgreSQL beispielsweise kann man die beiden gesetzten Constraints als "deferrable initially deferred" markieren, womit das theoretisch so klappen sollte, wie du es dir vorstellst.

In MySQL und MariaDB hast du noch die Alternative, statt "on delete cascade" einen "on before delete"-Trigger auf die users-Tabelle zu setzen, der die dazugehörigen Einträge aus den beiden anderen Tabellen entfernt.
 

JR Cologne

Well-Known Member
c-b Experte
#3
Alles klar, danke für die Erklärung.

In MySQL und MariaDB hast du noch die Alternative, statt "on delete cascade" einen "on before delete"-Trigger auf die users-Tabelle zu setzen, der die dazugehörigen Einträge aus den beiden anderen Tabellen entfernt.
Das werde ich mal versuchen.

Hat super funktioniert, nachdem ich ein bisschen Probleme mit der Syntax hatte:

SQL:
CREATE DATABASE IF NOT EXISTS `retask`;
USE `retask`;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_resets_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `task_list_id` int(10) unsigned DEFAULT NULL,
  `task` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tasks_user_id_index` (`user_id`),
  KEY `tasks_task_list_id_index` (`task_list_id`),
  CONSTRAINT `tasks_task_list_id_foreign` FOREIGN KEY (`task_list_id`) REFERENCES `task_lists` (`id`),
  CONSTRAINT `tasks_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `task_lists` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `task_lists_user_id_index` (`user_id`),
  CONSTRAINT `task_lists_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_name_unique` (`name`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `users_before_delete` BEFORE DELETE ON `users` FOR EACH ROW BEGIN
                DELETE FROM `tasks` WHERE `user_id` = `OLD`.`id`;
                DELETE FROM `task_lists` WHERE `user_id` = `OLD`.`id`;
            END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
 
Zuletzt bearbeitet:
Oben