mssql table multi foreign key cascade
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
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)