Конфликтующие требования внешнего ключа CASCADE и RESTRICT?

Я работаю над базой данных, которая отслеживает файлы и зависимости в проектах. Вкратце, у меня есть две основные таблицы; в таблице PROJECTS перечислены имена проектов и другие свойства, в таблице FILES перечислены файлы. Каждая запись файла указывает на проект как внешний ключ, установленный в CASCADE, поэтому, если я удалю запись проекта из базы данных, все записи файлов также исчезнут. Все идет нормально.

Теперь у меня есть дополнительная таблица DEPENDENCIES. Каждая запись в таблице зависимостей — это два файла, указывающих, что первый файл зависит от второго. Опять же, это внешние ключи, первый установлен в CASCADE (поэтому, если я удалю запись файла, эта запись будет удалена), а второй установлен в RESTRICT (поэтому мне не разрешено удалять запись файла, если какие-либо другие файлы зависят в теме). Опять вроде все хорошо.

К сожалению, кажется, я больше не могу удалить проект с помощью одного оператора удаления SQL! Удаление пытается выполнить каскадное удаление файлов, но если какие-либо из них появляются в таблице DEPENDENCIES, внешний ключ RESTRICT предотвращает удаление (даже если эта запись в таблице зависимостей будет удалена, поскольку другой столбец имеет значение CASCADE). Единственный обходной путь, который у меня есть, — рассчитать точный порядок удаления файлов, чтобы ни одно из ограничений записей зависимостей не нарушалось, и удалять записи файлов по одной, прежде чем пытаться удалить проект.

Есть ли способ настроить мою схему базы данных, чтобы одно удаление SQL из таблицы проектов корректно каскадировало другие удаления? Я использую Firebird 2.1, но я не знаю, имеет ли это какое-то значение — кажется, должен быть способ заставить это работать?


sql database database-design firebird
person Chris    schedule 09.12.2008    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Вы не можете управлять порядком удаления с помощью каскадного внешнего ключа, но вы можете создать триггер на PROJECTS для удаления строк в FILES, которые принадлежат этому проекту, а также перечислены в DEPENDENCIES как зависимые от других FILES. Сделайте его триггером BEFORE DELETE, чтобы он выполнялся до каскадных эффектов.

Что-то вроде этого:

CREATE TRIGGER Del_Child_Files FOR PROJECTS
BEFORE INSERT
AS BEGIN
  FOR SELECT F.FILE_ID FROM FILES F JOIN DEPENDENCIES D 
      ON F.FILE_ID = D.CHILD_ID
    WHERE F.PROJECT_ID = OLD.PROJECT_ID
    INTO :file_id
  DO
    DELETE FROM FILES WHERE FILE_ID = :file_id;
  DONE
END

Поэтому, когда вы удаляете проект, удаляются все «дочерние» файлы проекта, которые зависят от других файлов, и каскадно удаляются строки в DEPENDENCIES, поэтому все оставшиеся файлы свободны от зависимостей. Теперь ваше удаление проекта может привести к удалению этих файлов.

Я не проверял это, и мой синтаксис Firebird может быть ржавым, но, возможно, он поможет вам начать работу.

Очевидно, пожалуйста, проверьте это на копии ваших данных, а не на реальных данных!

person Bill Karwin    schedule 09.12.2008
comment
Мне определенно нравится идея сделать это в триггере, а не в коде приложения. Я попробую, и похоже, это будет ответ для меня :) - person Chris; 09.12.2008

arrow_upward
0
arrow_downward

Поддерживает ли система отложенные ограничения, в которых проверка ограничений может быть отложена до точки фиксации?

Может быть, это просто вещь Oracle.

person David Aldridge    schedule 09.12.2008
comment
Firebird не поддерживает отложенные ограничения. - person Bill Karwin; 09.12.2008