...продолжение
Особенности проектирования
(SQL Server)
Итак, в
этом посте будут затронуты особенности построения БД с использованием связи self
referencing many-to-many на SQL Server.
Все исходные
данные и условия те же, что и в первой части.
Допустим, мы создали две таблицы (пока без ограничений FK):
USE [stones1]
GO
CREATE TABLE [dbo].[stones](
[id] [smallint] NOT NULL,
[name] [nvarchar](35) NOT NULL,
CONSTRAINT [PK_stones_1]
PRIMARY KEY ([id] ASC)
)
GO
CREATE TABLE [dbo].[stf](
[stone_id] [smallint]
NOT NULL,
[form_id] [smallint] NOT NULL,
CONSTRAINT [PK_stf] PRIMARY KEY([stone_id] ASC,[form_id] ASC)
)
GO
Теперь наложим ограничения FK на поля отношения STF:
USE [stones1]
GO
ALTER TABLE [dbo].[stf] WITH CHECK ADD CONSTRAINT [FK_stf_stones_id1] FOREIGN
KEY([stone_id])
REFERENCES [dbo].[stones] ([id])
ON DELETE CASCADE
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[stf] WITH CHECK ADD CONSTRAINT [FK_stf_stones_form1] FOREIGN
KEY([form_id])
REFERENCES [dbo].[stones] ([id])
ON DELETE CASCADE
ON UPDATE CASCADE
GO
При попытке
выполнить эти пакеты вы получите сообщение об ошибке следующего вида:
Или, если задавать FK в конструкторе –
то приблизительно такое сообщение:
Как видим – SQL Server не позволяет спроектировать БД подобным
образом :( …
Согласно
информации по референсу http://support.microsoft.com/kb/321843/en-us?fr=1, (далее практически дословно) вы
получаете ошибку 1785 потому, что в СУБД SQL Server таблица не может появляться более
одного раза в списке всех каскадных ссылочных действий,
которые являются результатом вызова инструкций DELETE или UPDATE.
Более продвинутые
в MS SQL гуру на RSDN (http://www.rsdn.ru/forum/db/3081247.flat.aspx) обьясняют это
спецификой алгоритма определения циклов от мелкомягких, и это безобразие
тянется еще с 2000 года, и менять никто ничего принципиально не собирается
(конечно, лезть в старый код и переписывать рабочий алгоритм, со всеми вытекающими… я б сам не
стал :))
Кто виноват, и что делать?
Очевидно, что это упущение разработчиков мелкомягких. Ведь
поддерживание целостности данных на
максимально возможном уровне – одна из основных задач СУБД и она определена в стандарте ANSI/ISO. В данном случае, стараниями разработчиков, этот максимум занижен путем наложенного ограничения
функционала СУБД (видимо, перестарались). Можно конечно возразить, что конкретные реализации условий
целостности отличаются в разных СУБД, но “отличная от иной реализация” и “отсуствие реализации” – это разные вещи.
Решение данной
проблемы саппорт целиком и полностью возлагает на наши плечи :) (в том-же референсе предлагается использовать триггеры в случае необходимости обесппечения
целостности и максимальной гибкости). То же решение предлагается и на RSDN, и на SQL.RU (http://www.sql.ru/forum/actualthread.aspx?tid=595772).
Каковы
будут наши дальнейшие действия?
Сначала необходимо все-таки наложить ограничения внешнего
ключа на отношение STF. Для этого необходимо убрать из вышеприведеного листинга для FK правила обновления\удаления и выполнить
запрос. После этого, не мудрствуя лукаво, пишем триггеры.
Итак, для успешного удаления записи из таблицы STONES мы
должны сначала обработать таблицу STF, так
как у нас наложены ограничения внешних ключей
с правилами обновления и удаления RESTRICT (по умолчанию), и в случае непосредственной попытки выполнить
инструкцию DELETE - мы
получим сообщение об ошибке.
Простейший возможный
сценарий работы триггера можно составить таким образом:
- Проверить
количество связанных дочерних записей в таблице STF и в случае успеха – удалить.
- Проверить
количество связанных родительских записей в таблице STF и в случае успеха – также удалить.
- Удалить запись таблицы STONES
USE [stones1]
GO
CREATE TRIGGER [dbo].[trg_stones_IOD] ON [dbo].[stones]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.stone_id=deleted.id)>0
BEGIN
DELETE stf FROM stf, deleted WHERE stf.stone_id=deleted.id
END
IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.form_id=deleted.id)>0
BEGIN
DELETE stf FROM stf, deleted WHERE stf.form_id=deleted.id
END
DELETE stones FROM stones,deleted WHERE stones.id=deleted.id
END
GO
Триггер для обновления INSTEAD OF UPDATE пишется
аналогичным образом и здесь я его приводить не буду.
Вот и все. Мы добились требуемой архитектуры БД с
использованием связи many-to-many и
каскадным удалением\обновлением данных.
