• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

unable to add foreign key

So I am trying to add a foreign key to a table with three columns
one is an id column (the primary key) and the other two are foreign keys, or at least they are supposed to be. I try adding the last foreign key and i keep getting

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fk_id' on table 'tablename' 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. See previous errors.

However when i do no action i get

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_id". The conflict occurred in database "database", table "dbo.othertable", column 'id'.

It doesn't make sense, because they are both int's and they both have the same range of numbers.
 When i use the GUI it says it saved on the 'othertable' but failed on the one that needs the foreign key. Any ideas?
  • 4
1 Solution
dashifireAuthor Commented:
problem is that even if i don't do cascade it fails. I have removed the other foreign key before to make it work as well. No avail.
Basically your referential constraints are resulting in cyclic cascade and hence, the issue. The solution to this could be to create triggers.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

dashifireAuthor Commented:
Like i said though, when i remove the other foreign key, I still can't add in the new one. can it be cyclic with only one foreign key?
dashifireAuthor Commented:
'OTHERTABLE' table saved successfully
'TABLE' table
- Unable to create relationship 'FK_id'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_id". The conflict occurred in database "DATABASE", table "dbo.REFERED TABLE", column 'id'.

Its like it is trying to create it twice or something and then fails to do it once.
dashifireAuthor Commented:
Well sadly i have come to the conclusion there is no good way to work with this database using Foreign Keys, I will have to learn how to make triggers to simulate FKs

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now