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约束已经阻止了更新。
我想你可以实现一个 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): 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: This T-SQL gave me the following error: 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: They're simpler than your tables, but hopefully close enough. We need an immutable identifier in 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: And then the cascading update: Some simple inserts: Then the following gets an error. Not quite like a built in FK violation, but close enough: This is the update that we wanted to be able to perform: And we query the FK table: Result: So the update cascaded. Why you can't use real FKs: You want to have cascading updates. That means that the ID values in As such, we know that the new key value will not yet exist. If we were to attempt cascading updates using an I suppose you could implement an 这篇关于mssql表多外键级联的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! INSTEAD OF $ c $触发器,将新行插入为重复项,更新子项,然后删除旧的行。在这种情况下,我想你可以有真正的FK。但是我不想在所有情况下都写这个触发器(比如你有三行更新,两个交换ID值,另一个创建一个新的ID)
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
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
*'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.*
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
, and obviously the ID
s aren't it, since the whole point is to cascade changes to them. So I've added _RowID
.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)
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
select * from TabB
ID1a ID2a ID1b ID2b
----------- ----------- ----------- -----------
1 2 2 3
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.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.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)