Introducing FOREIGN KEY constraint! ! !

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

i inserted some foreign key but this one not work.
i inserted another foreign key in this table.

SQL

alter table ROOM
   add constraint FK_ROOM_REFERENCE_USER foreign key (USERID)
      references "USER" (USERID)
      on update cascade
      on delete cascade;

the error is :

C#

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?

Posted 28-Mar-16 22:23pm

saeed rajabi

Updated 28-Mar-16 22:31pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2016-03-28T22:29:00

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.

phil.o · Answer 2 · 2016-03-28T22:31:00

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.

Kindly.

Introducing FOREIGN KEY constraint! ! !

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0