Помогите реализовать сброс в NULL одного из двух полей внешнего ключа, при условии, что второе поле объявлено как NOT NULL (аналог ON DELETE SET NULL).
Задача. Есть список отчетов. Каждый отчет может выступать шаблоном для другого отчета. При удалении шаблона все отчеты, наследуемые от него, переводятся в разряд "без шаблона". Кроме того, с текущей конфигурации отчетов и шаблонов может быть сделана "точка восстановления" (вторая конфигурация). При удалении точки восстановления должны удаляться все ее отчеты и шаблоны.
Таблицы выглядят так:
Код: Выделить всё
CREATE TABLE CONFIGS (
CONFIG_ID INTEGER NOT NULL,
CONFIG_NAME CHAR(64),
PRIMARY KEY (CONFIG_ID));
CREATE TABLE REPORTS (
CONFIG_ID INTEGER NOT NULL,
REPORT_ID INTEGER NOT NULL,
REPORT_NAME CHAR(64),
TEMPLATE_ID INTEGER,
PRIMARY KEY (CONFIG_ID, REPORT_ID));
ALTER TABLE REPORTS ADD CONSTRAINT FK_REPORT_CONFIG FOREIGN KEY(CONFIG_ID) REFERENCES CFG_CONFIGS(CONFIG_ID) ON DELETE CASCADE ON UPDATE CASCADE;
Самое очевидное решение – это FK. Это правильно, т.к. ссылку на шаблон надо еще и обновлять при изменении ID шаблона (мало вероятно, но реально):
Код: Выделить всё
ALTER TABLE REPORTS ADD CONSTRAINT FK_REPORT_TEMPLATE FOREIGN KEY(CONFIG_ID, TEMPLATE_ID) REFERENCES REPORTS (CONFIG_ID, REPORT_ID) ON DELETE SET NULL ON UPDATE CASCADE;
Я могу предложить два других варианта:
1. Сделать FK с действием ON DELETE NO ACTION, на таблицу REPORTS сделать триггер BEFORE DELETE и сбрасывать TEMPLATE_ID вручную.
Этот вариант простой. Но как он поведет себя при одновременном удалении шаблона двумя пользователями в разных транзакциях? А если встанет вопрос о портировании БД на другую СУБД, у которой ограниченная поддержка триггеров?
2. Вынести связку шаблон-отчет в отдельную таблицу с cнормальным FK ON DELETE CASCADE:
Код: Выделить всё
CREATE TABLE REPORT_TEMPLATE (
CONFIG_ID INTEGER NOT NULL,
REPORT_ID INTEGER NOT NULL,
TEMPLATE_ID INTEGER NOT NULL,
PRIMARY KEY (CONFIG_ID, REPORT_ID, TEMPLATE_ID));
Какой вариант предпочтительнее? Какие плюсы и минусы я не увидел у предложенных вариантов? Может можно сделать по-другому?