unable to add foreign key

Posted on 2011-10-12
Last Modified: 2012-06-27
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?
Question by:dashifire
    LVL 29

    Expert Comment


    Author Comment

    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.
    LVL 13

    Accepted Solution

    Basically your referential constraints are resulting in cyclic cascade and hence, the issue. The solution to this could be to create triggers.

    Author Comment

    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?

    Author Comment

    '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.

    Author Closing Comment

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Scenario:  You do full backups to a internal hard drive in either product (SBS or Server 2008).  All goes well for a very long time.  One day, backups begin to fail with a message that the disk is full.  Your disk contains many, many more backups th…
    OfficeMate Freezes on login or does not load after login credentials are input.
    This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
    This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now