Гарантировать правильную запись по внешним ключам - MySQL

Формулировка задачи:

Ситуация: Есть таблицы tests, questions, answers, passed_test, passed_question_answer. test -> questions - один ко многим questions -> answers - один ко многим test -> passed_test - один ко многим passed_test -> passed_question_answer - один ко многим questions -> passed_question_answer - один ко многим answers -> passed_question_answer - один ко многим Cоздание:

                    Листинг программы
                
 
                            
                        
CREATE TABLE `tests` (
  `id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `tests`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `tests_name_unique` (`name`);CREATE TABLE `question` (
  `id` INT(10) UNSIGNED NOT NULL,
  `test_id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `text` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `question`
  ADD PRIMARY KEY (`id`),
  ADD KEY `question_test_id_foreign` (`test_id`);
 
ALTER TABLE `question`
  ADD CONSTRAINT `question_test_id_foreign` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;CREATE TABLE `answer` (
  `id` INT(10) UNSIGNED NOT NULL,
  `question_id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `correct` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `answer`
  ADD PRIMARY KEY (`id`),
  ADD KEY `answer_question_id_foreign` (`question_id`);
 
ALTER TABLE `answer`
  ADD CONSTRAINT `answer_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`);CREATE TABLE `passed_test` (
  `id` INT(10) UNSIGNED NOT NULL,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `test_id` INT(10) UNSIGNED NOT NULL,
  `started_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `passed_test`
  ADD PRIMARY KEY (`id`),
  ADD KEY `passed_test_user_id_foreign` (`user_id`),
  ADD KEY `passed_test_test_id_foreign` (`test_id`);
 
ALTER TABLE `passed_test`
  ADD CONSTRAINT `passed_test_test_id_foreign` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `passed_test_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;CREATE TABLE `passed_question_answer` (
  `id` INT(10) UNSIGNED NOT NULL,
  `passed_test_id` INT(10) UNSIGNED NOT NULL,
  `question_id` INT(10) UNSIGNED NOT NULL,
  `answer_id` INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `passed_question_answer`
  ADD PRIMARY KEY (`id`),
  ADD KEY `passed_question_answer_passed_test_id_foreign` (`passed_test_id`),
  ADD KEY `passed_question_answer_question_id_foreign` (`question_id`),
  ADD KEY `passed_question_answer_answer_id_foreign` (`answer_id`);
 
ALTER TABLE `passed_question_answer`
  ADD CONSTRAINT `passed_question_answer_answer_id_foreign` FOREIGN KEY (`answer_id`) REFERENCES `answer` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `passed_question_answer_passed_test_id_foreign` FOREIGN KEY (`passed_test_id`) REFERENCES `passed_test` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `passed_question_answer_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

При такой реализации получается что в таблицу

passed_question_answer

может попасть question_id который вовсе не принадлежит этому тесту (passed_test_id -> test_id), так же и answer_id (ответ) который никак не связан с question_id. Начал реализовывать в логике приложения использующего бд, то есть заранее проверять связаны ли ответ и вопрос, тест и вопрос и по результату проверки записывать или нет. В какой-то момент задумался... а можно ли решить это в самой бд... На этом моменте остановился и пока без идей.

Решение задачи: «Гарантировать правильную запись по внешним ключам»

Листинг программы

ALTER TABLE `passed_question_answer`
  ADD CONSTRAINT `passed_question_answer_answer_id_foreign` FOREIGN KEY (`answer_id`,`question_id`) REFERENCES `answer` (`id`,`question_id`) ON DELETE CASCADE ON UPDATE NO ACTION

Объяснение кода листинга программы

В данном коде происходит изменение таблицы passed_question_answer
Добавляется новый внешний ключ с именем passed_question_answer_answer_id_foreign
Указывается, что внешний ключ ссылается на столбцы answer_id и question_id таблицы answer
Устанавливаются действия для внешнего ключа при удалении и обновлении данных:
- при удалении записи в таблице answer соответствующие записи в таблице passed_question_answer также будут удалены (ON DELETE CASCADE)
- при обновлении записи в таблице answer соответствующие записи в таблице passed_question_answer не будут изменены (ON UPDATE NO ACTION)