Self referencing many-to-many на MS SQL Server и Access. Особенности. Часть 2.

...продолжение

Особенности проектирования (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 и каскадным удалением\обновлением данных.