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.

Answers


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)


Need Your Help

Sort Collection object by property in Java

java list sorting arraylist collections

If I want to sort the Collection below by a property called Order on the CSVInputHandler class, how do I do that? I tried the one a the very bottom with no luck. The error says The method

Close hover div only when clicked outside block

javascript jquery css

I have got a loginbox (blue) that opens when you hover over the text login (Yellow). The box stays open when you go to the loginbox but it closes when you leave it (Mouse in the green area).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.