mssql表多外键级联 [英] mssql table multi foreign key cascade

问题描述

我创建的是一个用户历史记录MSSQL表保持对用户和由谁进行的更改。此表包含两个引用我的另一个表（用户）的外键 - 一个用于受影响用户的fkey和另一个用于进行更改的用户的fkey。

我需要的是对（User）表进行的任何修改，以便在这个新表中级联和更新相应的条目。

新表（User_History）中的字段如下（每个用户由两个字段标识）：

pre $ code > Affected_User_House_Id - int
Affected_User_Id - int
Modified_By_User_House_Id - int
Modified_By_User_Id - int
Modification_Date - datetime
ModificationMade - ntext

除了ModificationMade外，每个字段都是主键。字段Modification_Date精确到1秒。
我遇到的问题是创建所述级联。
我已经尝试运行下面的T-SQL代码：

$ pre $ ALTER TABLE [User_History] ​​WITH CHECK
ADD CONSTRAINT [FK_User_History_User] FOREIGN KEY（[Affected_User_House_Id]，[Affected_User_Id]）
REFERENCES [User]（[User_House_Id]，[User_ID]）
ON UPDATE CASCADE
GO

ALTER TABLE [User_History] ​​CHECK CONSTRAINT [FK_User_History_User]
GO

ALTER TABLE [User_History] ​​WITH CHECK
ADD CONSTRAINT [FK_User_History_User_ModifiedBy] FOREIGN KEY（[Modified_By_User_House_Id]，[ [用户]（[User_House_Id]，[User_ID]）
ON UPDATE CASCADE
GO

ALTER TABLE [User_History] ​​CHECK CONSTRAINT [FK_User_History_User_ModifiedBy]
GO

这个T-SQL给了我以下错误：

  *'User'表保存成功
'User_History'表
  - 无法创建关系'FK_User_History_User_ModifiedBy'。 
引入表'User_History'上的FOREIGN KEY约束'FK_User_History_User_ModifiedBy'可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。 
无法创建约束。看到以前的错误。* 
  

如果我删除第二个ON UPDATE CASCADE将意味着Modified_By_User_House_Id和Modified_By_User_Id字段中的值将不会更新以匹配其在User表中引用的值。

我在迷失如何实现这个目标。

解决方案

您只能指定一个级联。这里试图用两个触发器模拟多个级联：

  create table TabA（
 ID1 int not null，
 ID2 int not null，
 _RowID int IDENTITY（1,1）not null，
约束PK_TabA PRIMARY KEY（ID1，ID2），
约束UQ_TabA__RowID UNIQUE（_RowID）
 ）
 go 
创建表TabB（
 ID1a int not null，
 ID2a int not null，
 ID1b int not null，
 ID2b int not null， 
约束PK_TabB PRIMARY KEY（ID1a，ID2a，ID1b，ID2b）
）
  

他们比你的桌子简单，但希望足够接近。我们在 TabA 中需要一个不可变的标识符，显然 ID 不是它，因为整个问题是级联改变他们。所以我已经添加了 _RowID 。

至少实现一个真正的外键并且模拟最重要的是级联的行为，但是一些简单的反思会证明FK总会被破坏。所以我们模拟它：

 在插入后在TabB 
上创建触发器FK_TabB_TabA，将
更新为
如果存在，则在
上设置nocount（
从
中插入
 * 
插入
 left join 
 TabA a 
 on 
 i.ID1a = a.ID1和
 i.ID2a = a.ID2 
 left join 
 TabA b 
 on 
 i.ID1b = b.ID1和
 i.ID2b = b.ID2 
其中
 a._RowID为空或
 b._RowID为空）
 begin 
 declare @Error varchar （max）
 set @Error ='INSERT语句与外键约束FK_TabB_TabA冲突。在数据库'+ DB_NAME（）+'，表dbo.TabB。'
 RAISERROR（@ Error，16,0）
 rollback 
 end 
  

然后级联更新：

 < code创建触发器FK_TabB_TabA_Cascade TabA 
更新后
为
设置nocount 
 $ b $;更新为（
 select 
 d.ID1作为OldID1，
 d.ID2作为OldID2，
 i.ID1作为NewID1，
 i.ID2作为NewID2 
从
插入到
内部连接
删除d 
 
 i._RowID = d._RowID 
）
更新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 
 Updat $ u $ 
 
 b.ID1a = u1.OldID1和
 b.ID2a = u1.OldID2 
加入
更新
 
 b.ID1b = u2.OldID1和
 b.ID2b = u2.OldID2 
其中
 u1.OldID1不为空或
 u2.OldID1不为空
 go 
  

一些简单的插入：

<$ p $插入到TabA（ID1，ID2）
values（1,1），（1,2），（2,1），（2,2）
go
插入TabB（ID1a，ID2a，ID1b，ID2b）
值（1,1,2,2）

然后下面的错误。不像FK违例，但足够接近：

pre $ 插入TabB（ID1a，ID2a，ID1b，ID2b）
值（1,1,2,3）
--Msg 50000，级别16，状态0，过程FK_TabB_TabA，行28
- INSERT语句与外键约束冲突FK_TabB_TabA 。冲突发生在数据库法兰，表dbo.TabB。
--Msg 3609，等级16，状态1，行1
- 交易在触发器中结束。该批已被中止。


这是我们希望能够执行的更新：

  update TabA set ID2 = ID2 + 1 
  

我们查询FK表：

  select * from TabB 
  

结果：

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

所以更新层叠。

< hr>

为什么你不能使用真正的FK：

你想要级联更新。这意味着 TabA 中的ID值将会变成一个新的值（目前不存在）（注意 - 我们排除了2n行交换其身份值的情况） - 否则，主键约束将被这个更新中断。

因此，我们知道新的键值不会存在。如果我们使用 INSTEAD OF 触发器来尝试级联更新（在父级之前更新子级表），那么我们试图更新到 TabB 还不存在。或者，如果我们尝试使用 AFTER 触发器进行级联更新，那么我们为时已晚。 FK约束已经阻止了更新。

我想你可以实现一个 INSTEAD OF

I'm confident that this is possible but for the life of me I can't figure it out.

What I have created is a user history MSSQL table to hold the changes made to a user and by whom. This table contains two foreign keys which reference my other table (User) - one fkey for the affected user and the other fkey for the user making the changes.

What I need is for any changes to the (User) table to cascade and update the corresponding entries in this new table.

The fields in the new table (User_History) are as follows (Each user is identified by two fields):

Affected_User_House_Id  - int
Affected_User_Id - int
Modified_By_User_House_Id - int
Modified_By_User_Id – int
Modification_Date - datetime
ModificationMade - ntext

Each field is a primary key except for ‘ModificationMade’. The field ‘Modification_Date’ is accurate down to 1 second. The problem I am having is creating said cascade. I have tried running the following T-SQL code:

ALTER TABLE [User_History] WITH CHECK
ADD CONSTRAINT [FK_User_History_User] FOREIGN KEY([Affected_User_House_Id], [Affected_User_Id])
REFERENCES [User] ([User_House_Id], [User_ID])
ON UPDATE CASCADE
GO

ALTER TABLE [User_History] CHECK CONSTRAINT [FK_User_History_User]
GO

ALTER TABLE [User_History]  WITH CHECK
ADD CONSTRAINT [FK_User_History_User_ModifiedBy] FOREIGN KEY([Modified_By_User_House_Id], [Modified_By_User_Id])
REFERENCES [User] ([User_House_Id], [User_ID])
ON UPDATE CASCADE
GO

ALTER TABLE [User_History] CHECK CONSTRAINT [FK_User_History_User_ModifiedBy]
GO

This T-SQL gave me the following error:

*'User' table saved successfully
'User_History' table
- Unable to create relationship 'FK_User_History_User_ModifiedBy'.  
Introducing FOREIGN KEY constraint 'FK_User_History_User_ModifiedBy' on table 'User_History' may     cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or     modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.*

The code works if I remove the second "ON UPDATE CASCADE" the however that will mean the values in the fields "Modified_By_User_House_Id" and "Modified_By_User_Id" will not be updated to match their referenced values in the User table.

I am at a lost as to how to acomplish this goal.

解决方案

You can only specify a single cascade. Here's an attempt to simulate multiple cascades with two triggers:

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)
)

They're simpler than your tables, but hopefully close enough. We need an immutable identifier in TabA, and obviously the IDs aren't it, since the whole point is to cascade changes to them. So I've added _RowID.

It would be nice to implement at least a real foreign key and just simulate the cascade behaviour on top of that, but some simple reflection will demonstrate that there's always a point where the FK would be broken. So we simulate it:

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

And then the cascading update:

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

Some simple inserts:

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)

Then the following gets an error. Not quite like a built in FK violation, but close enough:

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.

This is the update that we wanted to be able to perform:

update TabA set ID2 = ID2 + 1

And we query the FK table:

select * from TabB

Result:

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

So the update cascaded.

---

Why you can't use real FKs:

You want to have cascading updates. That means that the ID values in TabA are going to change to a new value that doesn't currently exist (caveat - we're excluding situations where 2n rows swap their identity values) - otherwise, the primary key constraint will be broken by this update.

As such, we know that the new key value will not yet exist. If we were to attempt cascading updates using an INSTEAD OF trigger (to update the child table before the parent) then the new values we attempt to update to in TabB do not yet exist. Alternately, if we attempt to do cascading updates using an AFTER trigger - well, we're too late. The FK constraint has already prevented the update.

I suppose you could implement an INSTEAD OF trigger that inserts the new rows as "duplicates", updates the children, then deletes the old rows. In such a circumstance, I think you could have real FKs. But I don't want to try writing that trigger to be right in all circumstances (e.g where you have three rows being updated. Two swap their ID values and the other creates a new ID)

这篇关于mssql表多外键级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！