none
cascade update on a foreign key

    Question

  • I have a foreign key declared on one of my tables as follows:

    ALTER TABLE [dbo].[INSURE]  WITH CHECK ADD  CONSTRAINT [fk_profile_key] FOREIGN KEY([COMPANY], [E_COMP])
    REFERENCES [dbo].[profile] ([company], [e_comp])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO

    When I try to create this constraint i get an error message:

    Cannot define foreign key constraint 'fk_profile_key' with cascaded DELETE or UPDATE on table 'INSURE'
    because the table has an INSTEAD OF DELETE or UPDATE TRIGGER defined on it.

    Now the table does have an after update and instead of update triggers defiend for it. Does this mean I can have cascade update of these columns in this table. Is there some way I can acheive that besides writing code that will update all child records before it updates the parent record in the table these two columns are refering to? is there some setting I can set that will allow the cascade update on this table? can someone help please?

    Friday, October 19, 2012 7:35 PM

Answers

  • The error message speaks for itself. You will have to choose. Cascading FKs or that funny INSTEAD OF trigger in your other thread. I would not have any problems sacrificing that trigger.

    On the other hand, there are many other restrictions with cascading FKs, so it may not take long until you run into the next.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 19, 2012 10:11 PM

All replies