Каскад с несколькими внешними ключами в таблице mssql

Вы можете указать только один каскад. Вот попытка смоделировать несколько каскадов с двумя триггерами:

create table TabA (
    ID1 int not null,
    ID2 int not null,
    _RowID int IDENTITY(1,1) not null,
    constraint PK_TabA PRIMARY KEY (ID1,ID2),
    constraint UQ_TabA__RowID UNIQUE (_RowID)
)
go
create table TabB (
    ID1a int not null,
    ID2a int not null,
    ID1b int not null,
    ID2b int not null,
    constraint PK_TabB PRIMARY KEY (ID1a,ID2a,ID1b,ID2b)
)

Они проще, чем ваши таблицы, но, надеюсь, достаточно близки. Нам нужен неизменяемый идентификатор в TabA, и, очевидно, ID это не так, поскольку весь смысл в том, чтобы каскадировать их изменения. Поэтому я добавил _RowID.

Было бы неплохо реализовать по крайней мере реальный внешний ключ и просто смоделировать поведение каскада поверх него, но некоторое простое размышление продемонстрирует, что всегда есть точка, в которой FK будет нарушен. Итак, мы моделируем это:

create trigger FK_TabB_TabA on TabB
after insert,update
as
    set nocount on
    if exists (
        select
            *
        from
            inserted i
                left join
            TabA a
                on
                    i.ID1a = a.ID1 and
                    i.ID2a = a.ID2
                left join
            TabA b
                on
                    i.ID1b = b.ID1 and
                    i.ID2b = b.ID2
        where
            a._RowID is null or
            b._RowID is null)
    begin
        declare @Error varchar(max)
        set @Error = 'The INSERT statement conflicted with the Foreign Key constraint "FK_TabB_TabA". The conflict occurred in database "'+DB_NAME()+'", table "dbo.TabB".'
        RAISERROR(@Error,16,0)
        rollback
    end

И затем каскадное обновление:

create trigger FK_TabB_TabA_Cascade on TabA
after update
as
    set nocount on

    ;with Updates as (
        select
            d.ID1 as OldID1,
            d.ID2 as OldID2,
            i.ID1 as NewID1,
            i.ID2 as NewID2
        from
            inserted i
                inner join
            deleted d
                on
                    i._RowID = d._RowID
    )
    update b
    set
        ID1a = COALESCE(u1.NewID1,ID1a),
        ID2a = COALESCE(u1.NewID2,ID2a),
        ID1b = COALESCE(u2.NewID1,ID1b),
        ID2b = COALESCE(u2.NewID2,ID2b)
    from
        TabB b
            left join
        Updates u1
            on
                b.ID1a = u1.OldID1 and
                b.ID2a = u1.OldID2
            left join
        Updates u2
            on
                b.ID1b = u2.OldID1 and
                b.ID2b = u2.OldID2
    where
        u1.OldID1 is not null or
        u2.OldID1 is not null
go

Несколько простых вставок:

insert into TabA (ID1,ID2)
values (1,1),(1,2),(2,1),(2,2)
go
insert into TabB (ID1a,ID2a,ID1b,ID2b)
values (1,1,2,2)

Затем следующее получает ошибку. Не совсем похоже на встроенное нарушение FK, но достаточно близко:

insert into TabB (ID1a,ID2a,ID1b,ID2b)
values (1,1,2,3)
--Msg 50000, Level 16, State 0, Procedure FK_TabB_TabA, Line 28
--The INSERT statement conflicted with the Foreign Key constraint "FK_TabB_TabA". The conflict occurred in database "Flange", table "dbo.TabB".
--Msg 3609, Level 16, State 1, Line 1
--The transaction ended in the trigger. The batch has been aborted.

Это обновление, которое мы хотели выполнить:

update TabA set ID2 = ID2 + 1

И мы запрашиваем таблицу FK:

select * from TabB

Результат:

ID1a        ID2a        ID1b        ID2b
----------- ----------- ----------- -----------
1           2           2           3

Так что обновление пошло каскадом.

Почему нельзя использовать настоящие FK:

Вы хотите иметь каскадные обновления. Это означает, что значения идентификатора в TabA будут изменены на новое значение, которого в настоящее время не существует (предостережение — мы исключаем ситуации, когда 2n строк меняют свои значения идентификаторов) — в противном случае ограничение первичного ключа будет нарушено этим Обновить.

Таким образом, мы знаем, что новое значение ключа еще не будет существовать. Если бы мы попытались выполнить каскадные обновления с помощью триггера INSTEAD OF (чтобы обновить дочернюю таблицу перед родительской), то новые значения, которые мы пытаемся обновить в TabB, еще не существуют. В качестве альтернативы, если мы попытаемся выполнить каскадные обновления с помощью триггера AFTER — что ж, мы опоздали. Ограничение FK уже предотвратило обновление.

Я полагаю, что вы могли бы реализовать триггер INSTEAD OF, который вставляет новые строки как "дубликаты", обновляет дочерние элементы, а затем удаляет старые строки. В таких обстоятельствах, я думаю, у вас могут быть настоящие FK. Но я не хочу пытаться написать этот триггер, чтобы он был правильным при любых обстоятельствах (например, когда у вас есть три обновляемых строки. Два меняют свои значения идентификатора, а другой создает новый идентификатор)

Согласно этой статье базы знаний, это сообщение об ошибке появляется, когда "таблица не может появляться более одного раза в списке всех каскадных ссылочных действий, которые запускаются либо оператором DELETE, либо оператором UPDATE».

Поскольку у вас есть два пути, исходящих из одной и той же таблицы, возможный обходной путь может заключаться в создании нового ключа в родительской таблице и создании одного внешнего ключа в дочерней ([Affected_User_House_Id], [Affected_User_Id], [Modified_By_User_House_Id], [Modified_By_User_Id]). Однако это, вероятно, создаст много накладных расходов. В крайнем случае вы можете использовать триггеры для обеспечения реляционной целостности.