Multiple Cascade Paths

Question

I have a problem with multiple cascade path error. Here are my tables:

Table (Companies)
CompanyCode (PK)
....

Table (Aircraft)
AircraftRegistration (PK)
OwnerCode (FK to CompanyCode)
OperatorCode (FK to CompanyCode)
....

I simply want to update the ownercode and operatorcode foreign keys in the aircraft table when I update the primary key in companies.

Is the correct way to get around this problem to use triggers?

I think triggers is the only way to deal with multiple cascade paths in SQL-Server. — ypercubeᵀᴹ, May 16 '12 at 10:37

Pondlife · Accepted Answer · 2012-05-16T07:03:32.687

2

You can add ON UPDATE CASCADE to your foreign key definitions, then the values will automatically be updated if the referenced key (i.e. Companies.CompanyCode) is changed.

EDIT: But as you noted in the comments, this won't work for tables that have two foreign keys referencing the same column, so for those cases you would have to use a trigger or do all your updates in a 'controlled' manner, such as through a stored procedure that updates the referencing columns. Which approach is better depends on your application design and how your database is used.

edited May 16 '12 at 07:03

answered May 15 '12 at 11:35

Pondlife

15,992
6
37
51

That's what I tried but it gives me the error '...may cause cycles or multiple cascade paths". This is because I have two FK's to the same table, "OwnerCode" and "Operator Code". – Kiwi May 16 '12 at 00:34
My mistake, I didn't pick up on that point immediately. I've updated my answer. – Pondlife May 16 '12 at 07:07

Multiple Cascade Paths

1 Answers1

Linked