Click here to Skip to main content
15,849,494 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i inserted some foreign key but this one not work.
i inserted another foreign key in this table.
alter table ROOM
   add constraint FK_ROOM_REFERENCE_USER foreign key (USERID)
      references "USER" (USERID)
      on update cascade
      on delete cascade;

the error is :

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_ROOM_REFERENCE_USER' on table 'ROOM' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

when i put it no action it will be work
but on cascade no!

What I have tried:

i inserted all of tables and foreign key but two foreign key not inserted.
in room table i have 3 foreign key.
one of them inserted.
two number of my code not working that one of them i wrote up.
what should i do?
Updated 28-Mar-16 23:31pm

Start by looking at your data in all tables, and pay attention to what foreign keys already exist. The most likely reason for this is that you have set up a relationship where a loop exists, so that TableA has an FK to TableB, and TableB has an FK to TableA (or via TableC, etc) - so the cascades end up trying to loop because deleting from one table causes a delete on the second, which causes the first to delete, and so forth.
Share this answer
saeed rajabi 29-Mar-16 4:31am    
ok ill try.
OriginalGriff 29-Mar-16 4:33am    
You're welcome!
The last foreign key constraint you created introduced a circular reference between your tables.
Circular references are not accepted if the DELETE/UPDATE actions are set to anything else than "NO ACTION" for more than one relation.
You may have to follow the advise given in the error message. Or, you would have to modify the design of your database to get rid of the circular reference; we cannot provide any other advise at this stage, as we do not know the actual architecture of your database.

Share this answer
saeed rajabi 29-Mar-16 4:34am    
ill looking for the way!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900