Два внешних ключа ссылаются на один стол – ON UPDATE SET NULL не работает

У меня есть два внешних ключа в таблице. Предположим, что таблица называется News и имеет внешние ключи updatedById и createdById , оба из которых указывают на userId в таблице Users .

Теперь, когда пользователь удаляется, я хочу установить NULL внешние ключи, но когда я пытаюсь установить ON DELETE SET NULL в этих отношениях, я получаю:

Представление ограничения FOREIGN KEY «FK_News_Users» в таблице «Новости» может вызывать циклы или несколько каскадных путей. Укажите ON DELETE NO ACTION или ON UPDATE NO ACTION или измените другие ограничения FOREIGN KEY.

Я не понимаю, почему оба внешних ключа не могут установить значение null?

Несколько каскадных действий

Ряд каскадных ссылочных действий, инициируемых одним DELETE или UPDATE, должен сформировать дерево, которое не содержит циклических ссылок. Ни одна таблица не может появляться более одного раза в списке всех каскадных ссылочных действий, которые являются результатом DELETE или UPDATE. Кроме того, дерево каскадных ссылочных действий не должно иметь более одного пути к любой указанной таблице. Любая ветвь дерева заканчивается, когда встречается таблица, для которой NO ACTION задано или является значением по умолчанию.

Возможно, в таких ситуациях вам может потребоваться внедрить функциональные возможности для удаления пользователя логически, а не физически (например, путем введения поля поля «Active» или «Deleted in Users ). Таким образом, все отношения остаются нетронутыми и могут быть проанализированы ретроспективно.

Но если вам еще нужно реализовать ON DELETE SET NULL для обоих FK, вы можете использовать триггер FOR DELETE в таблице User следующим образом:

 CREATE TRIGGER Users_News_Delete_Trigger ON Users FOR DELETE AS BEGIN UPDATE News SET createdById = NULL WHERE createdById = DELETED.id; UPDATE News SET updatedById = NULL WHERE updatedById = DELETED.id; END 

Один из вариантов заключается в создании таблицы перекрестных ссылок между таблицей A и таблицей B, где каждая запись является A.ID и B.ID, а B.ID имеет внешний ключ для B. Тогда вы можете просто CASCADE удалять ссылку. Вам нужно будет поместить третье поле в свою перекрестную ссылку, чтобы указать уникальную цель ссылки, такую ​​как

 [NewsID] INT NOT NULL DEFAULT 0, [UsersID] INT NOT NULL DEFAULT 0, [IsCreatedBy] bit NOT NULL DEFAULT 0 

Естественно, вы должны были бы вывести эти поля из таблицы A. Левое объединение затем даст вам null для этих полей, если они отсутствуют.

Я не думаю, что это возможно (в SQL Server) сделать это на 2 или более ограничениях FK в одной таблице, указывая на тот же FK.

Обычно в таких ситуациях вы скорее удаляете пользователя логически, а затем физически, вводите поле флага (например, Active или Deleted). Таким образом, все отношения остаются нетронутыми и могут быть проанализированы ретроспективно. — peterm

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

 CREATE PROCEDURE sp_DeleteUser @UserId INT AS BEGIN SET NOCOUNT ON; DELETE FROM Users WHERE Id = @UserId; UPDATE News SET created_byId = NULL WHERE created_byId = @UserId; UPDATE News SET updated_byId = NULL WHERE created_byId = @UserId; END GO 
  • Кондитируйте для проверки ограничений
  • Как узнать, является ли CONSTRAINT_NAME основным или внешним ключом?
  • SQL Server: удаление строк с ограничениями внешнего ключа: могут ли транзакции переопределять ограничения?
  • Есть ли способ избежать удаления строк в конкретной таблице с помощью ограничений или триггеров?
  • SQL для предотвращения несовпадающих внешних ключей
  • Переопределение максимального значения типа данных bigint в SQL Server
  • Как создать уникальное ограничение SQL на основе двух столбцов?
  • Запрос, чтобы найти все ограничения FK и их правила удаления (SQL Server)
  • Добавление ограничений именованного внешнего ключа в инструкции SQL Create
  • Проверить ограничение - разрешить только один столбец, если другой столбец верен
  • Поиск всех нулевых столбцов в базе данных SQL 2000
  • Давайте будем гением компьютера.