locked
Basic Create foreign Key issue RRS feed

  • Question

  • Hi All,

    I am getting an error when I try to create a foreign key.

    Scenario:
    I have tables A, B and C. Table A has a prim key on Aid column and Table B has a prim key on Bid column and Table C has a prim key on Cid column. 

    Relationship:
    Aid is the foreign key for tables B and C and there is a relationship between A and B and another relation between A and C.

    Issue:
    Table C has a column Bid and I want to create a foreignkey relationship between B and C. and below is my sql

    ALTER TABLE C
    ADD Constraint FK_C_B FOREIGN KEY (BId) REFERENCES B(BId)


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

     

    Will be thankful if you can shed some light on me.
    Wednesday, January 18, 2012 5:35 PM

Answers

  • Hello All,

    Apologize for not replying back. I figured out the solution same day. The database was being designed with data in it and when the developer asked me this question I was under the assumption that he was working on designing an empty database. The issue is it was because of bad data in it, once the bad data got deleted I was able to create the foreign key.

    Thanks for all your time. 

     

    • Marked as answer by Stephanie Lv Thursday, February 2, 2012 1:33 AM
    Monday, January 23, 2012 7:14 PM

All replies

  • If B is related to A, and C is related to B, there is no reason to relate C to A directly.
    Wednesday, January 18, 2012 6:42 PM
    Answerer
  • I do not know your business requirements but all you described works just fine

     

    create table #a (aid int not null primary key)

    create table #b (bid int not null primary key,aid int foreign key references #a(aid))

    create table #c (cid int not null primary key,aid int foreign key references #a(aid)

                     ,bid int foreign key references #b(bid) )


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 19, 2012 8:51 AM
  • If B is related to A, and C is related to B, there is no reason to relate C to A directly.


    Hmmm.  I have never tested whether or not optimizer does join eliminations for transitive tables that do not have the transitive relationship explicitly expressed.  I will look into this a little later.  If this is the case then this might be a reason to include a transitive relationship?

    EDIT:

    Good news: SQL Server 2008 R2 does in fact properly infer the transitive relationship and does the join elimination when no explicity foreign key is established from C to A.

    :)



    Thursday, January 19, 2012 1:29 PM
  • Sure, that would be very interesting to know. Please do post your findings.

    But from a design standpoint, it should not matter.

    Thursday, January 19, 2012 3:03 PM
    Answerer
  • Hello,

    Can you please inform that whether the tableC contains data which is invalid i.e. the BID column in tableC has some invalid data in BID column

    Monday, January 23, 2012 1:50 PM
  • Hello All,

    Apologize for not replying back. I figured out the solution same day. The database was being designed with data in it and when the developer asked me this question I was under the assumption that he was working on designing an empty database. The issue is it was because of bad data in it, once the bad data got deleted I was able to create the foreign key.

    Thanks for all your time. 

     

    • Marked as answer by Stephanie Lv Thursday, February 2, 2012 1:33 AM
    Monday, January 23, 2012 7:14 PM