ON DELETE SET NULL у FK по 2 полям, одно из которых NOT NULL

Модераторы: kdv, CyberMax

Ответить
checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

ON DELETE SET NULL у FK по 2 полям, одно из которых NOT NULL

Сообщение checker » 19 янв 2011, 09:44

Здравствуйте!

Помогите реализовать сброс в 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;
Теперь собственно сам вопрос: как лучше организовать связь шаблон-отчет одной конфигурации, чтобы автоматически сбрасывать поле TEMPLATE_ID при удалении записи шаблона?

Самое очевидное решение – это 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;
Но такой FK не даст удалять шаблоны, поскольку при удалении записи шаблона FK попытается сбросить поле CONFIG_ID в NULL, а оно объявлено как NOT NULL.

Я могу предложить два других варианта:

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));
В этом варианте получаются более сложные запросы для выборки отчетов. Для получения номера шаблона в списке отчетов надо делать JOIN, вместо UPDATE для назначения номера шаблона надо делать INSERT в другую таблицу с возможной ошибкой DUPLICATE …, если вставку делают одновременно два пользователя и т.п.

Какой вариант предпочтительнее? Какие плюсы и минусы я не увидел у предложенных вариантов? Может можно сделать по-другому?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение kdv » 19 янв 2011, 13:59

либо никто ничего не понял, либо неинтересно затевать дискуссию по чужим прикладным проблемам.

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение checker » 19 янв 2011, 14:33

kdv писал(а):либо никто ничего не понял, либо неинтересно затевать дискуссию по чужим прикладным проблемам.
Ладно, перефразирую вопрос.

Есть таблица, в ней два поля, одно из них объявлено как NOT NULL. По этим двум полям создан внешний ключ на вторую таблицу.
Требуется при удалении удалении записи из второй таблицы сбросить в NULL в соответствующих записях первой первой таблицы только одно поле ключа, а другое оставить как есть.

Как это лучше сделать?

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение hvlad » 19 янв 2011, 14:41

checker писал(а):Есть таблица, в ней два поля, одно из них объявлено как NOT NULL. По этим двум полям создан внешний ключ на вторую таблицу.
Требуется при удалении удалении записи из второй таблицы сбросить в NULL в соответствующих записях первой первой таблицы только одно поле ключа, а другое оставить как есть.

Как это лучше сделать?
Не создавать FK по двум полям ? В смысле - создавать только по одному.

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение checker » 19 янв 2011, 15:17

hvlad писал(а):Не создавать FK по двум полям ? В смысле - создавать только по одному.
Нет, FK должен быть с двумя полями (зачем это надо описано в первом сообщении темы, если интересно).

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение hvlad » 19 янв 2011, 16:22

checker писал(а):
hvlad писал(а):Не создавать FK по двум полям ? В смысле - создавать только по одному.
Нет, FK должен быть с двумя полями (зачем это надо описано в первом сообщении темы, если интересно).
Таких "частичных" FK не бывает.

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение checker » 19 янв 2011, 16:37

hvlad писал(а):Таких "частичных" FK не бывает.
Да, я знаю. Поэтому и спрашивал в первом сообщении: если я сделаю FK ON DELETE NO ACTION, а сброс полей ключа сделаю в триггере BEFORE DELETE внешней таблицы, то какие будут подводные камни?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: ON DELETE SET NULL у FK по 2 полям, одно из которых NOT

Сообщение Dimitry Sibiryakov » 20 янв 2011, 14:38

Никаких камней.

Ответить