SQL Server Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

I have two tables: T_User and T_Order

T_User
--------
ID
Name

T_Order has to foreign keys to T_User

T_Order
--------
ID
FK_UserActionOwnerID
FK_UserActionReceiverID

I made two relations then on my diagram from T_User ID to both FK's in T_Order. I also set for both relationships delete and update rules to cascade because i want if T_User record will be deleted so therefore records in T_Order should be deleted or if T_User ID would change then also update it in T_Order. Nevertheless i get following error:

'T_User' table saved successfully 'T_Order' table - Unable to create relationship 'FK_T_Order_T_Users1'. Introducing FOREIGN KEY constraint 'FK_T_Order_T_Users1' on table 'T_Order' 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 or index. See previous errors.

Real diagram: click here

Possible duplicate of [Foreign key constraint may cause cycles or multiple cascade paths?](https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) — Martin Brown, Jun 05 '19 at 10:44
@MartinBrown I read that but to be honest i do not know how to sovle that problem. It's like T_Order has fk about who made Order FK_UserActionOwnerID but also has a reference to whom this goes which means i need two foreign keys. Also with delete/update to cascae. How to sovle that then if i am not able to make two references from one table to another one? — Eldorado, Jun 05 '19 at 10:50
The usual way is to first create the FK constraints without cascades then create triggers to do the cascade as you wish them to happen. — Martin Brown, Jun 05 '19 at 11:15
@MartinBrown i uploaded diagram screenshoot - look there i marked by red line what i want to do additionally but it raise error i described. — Eldorado, Jun 05 '19 at 11:17
So you are saying you don't know how to create a foreign key without cascade in the diagram designer? — Martin Brown, Jun 05 '19 at 11:20
@MartinBrown I also have cascade for upadte/delete for relationship between T_Order and T_OrderItem. Should then i just make two relatshions beyween T_Users and T_Order without any cascade (means no action for update/delete) and prepare trigger, but relatsion between T_Order and T_OrderItem set this relationshion to cascade for update/delete? Is my understanding correct? — Eldorado, Jun 05 '19 at 11:21
The error message makes the suggestion to try again, but then to explicitly mark the Foreign Key to not perform any cascading action upon UPDATE or DELETE. — Gert-Jan, Jun 05 '19 at 11:23
"I also have cascade for update/delete for relationship between T_Order and T_OrderItem. Should then I just make two relations between T_Users and T_Order without any cascade (means no action for update/delete) and prepare trigger, but relation between T_Order and T_OrderItem set this relation to cascade for update/delete?" Yes that's what I would try. — Martin Brown, Jun 05 '19 at 11:26
so it just means i cannot have cascades set up when having two relatshions in one table to another. Could you be so kind and show me as an answer certainly how this trigger could look like? Ahh and what about update? I have T_User ID set to PK AI but just hipotetically if id would change for ID in T_User would this trigger also update it in T_Order? — Eldorado, Jun 05 '19 at 11:29

score 1 · Accepted Answer · answered Jun 05 '19 at 11:32

1

The Foreign key constraint may cause cycles or multiple cascade paths? question linked to by @Martin-Brown suggests using triggers instead of cascading foreign keys. Here's an example of using an INSTEAD OF DELETE trigger to do what (I think) you want to do.

-- Create tables
create table dbo.T_User (
    ID int identity not null primary key,
    Name varchar(100) not null
)

create table dbo.T_Order (
    ID int identity not null primary key,
    FK_UserActionOwnerID int not null,
    FK_UserActionReceiverID int not null
)
go

-- Create foreign keys
alter table dbo.T_Order add constraint FK_T_Order_T_Users1 FOREIGN KEY (FK_UserActionOwnerID) REFERENCES dbo.T_User (ID) 
alter table dbo.T_Order add constraint FK_T_Order_T_Users2 FOREIGN KEY (FK_UserActionReceiverID) REFERENCES dbo.T_User (ID) 
go

-- Create trigger
create trigger tr_T_User_Delete on dbo.T_User instead of delete as
begin

    if (@@rowcount = 0) return

    delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_UserActionOwnerID

    delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_UserActionReceiverID

    delete u from dbo.T_User u inner join deleted d on d.ID = u.ID  

end
go

-- Demo
insert dbo.T_User (Name) values ('Peter'), ('Paul') -- Assume identity ID 1 and 2

insert dbo.T_Order (FK_UserActionOwnerID, FK_UserActionReceiverID) values (1, 1), (1, 2), (2, 2)

select * from dbo.T_Order

delete from dbo.T_User where ID = 1

select * from dbo.T_Order

You can use INSTEAD OF UPDATE triggers in the same way but you might want to have a think about whether it makes sense for IDs to be updated - I wouldn't normally expect this.

answered Jun 05 '19 at 11:32

Rhys Jones

5,348
1
23
44

what about T_Order and T_OrderItem ? Should i leave connection between those tables as it is means delete/update rules as cascade? – Eldorado Jun 05 '19 at 12:40
@Eldorado Yes, you can keep the FK with cascade rules between T_Order and T_OrderItem, or use a trigger like above - it's up to you. – Rhys Jones Jun 05 '19 at 13:12
i get this error (before i removed all update/delete to "No Action" so i do not understand why getting this error: : tr_T_User_Delete, Line 1 [Batch Start Line 2] Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'tr_T_User_Delete2' on table 'T_Users'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE. – Eldorado Jun 06 '19 at 06:10
@Eldorado That error is quite clear - you can't have an INSTEAD OF ... trigger as well as a foreign key with cascading actions on the same table. One or the other, not both. – Rhys Jones Jun 06 '19 at 06:25
I had to recreate both tables from scratch, then i created them again and run your trigger script - now it works. Nevertheless i cannot see now relationship between those two tables on diagram - i understand because there are no now foreign keys but just external trigger. I see one issue here let's imagine external application and lets assume someone added id of user in order that not exist. Since we do not have fks it will be possible, any workaround? – Eldorado Jun 06 '19 at 07:34
@Eldorado You should still create the FKs but without any ON DELETE ... ON UPDATE ... clause. – Rhys Jones Jun 06 '19 at 08:26
but when i want to draw lines in diagram between order and user fields and then trying to save diagram it says: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_T_Order_T_Users". The conflict occurred in database "my", table "dbo.T_Users", column 'ID'." – Eldorado Jun 06 '19 at 08:29
ok i think it was because i had some records, now deleting them from both tables it's possible, so pelase of your confirmation - i have now two foreign keys in Order table and one ID in user which they are referencing, both upadte/delete set to "No action" and your trigger. Is it ok now? – Eldorado Jun 06 '19 at 08:38
ok i think it was because i had some records, now deleting them from both tables it's possible, so pelase of your confirmation - i have now two foreign keys in Order table and one ID in user which they are referencing, both upadte/delete set to "No action" and your trigger. Seems to work as expected. You think it's ok as well this way? – Eldorado Jun 06 '19 at 08:45
@Eldorado That sounds OK. It would be a good idea to learn how to build your database using T/SQL scripts instead of the diagram tool - it's a lot easier :) – Rhys Jones Jun 06 '19 at 11:15

SQL Server Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

1 Answers1