Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Multiple foreignkey in single column

    Hi Friends,

    I have a problem in creating a table with a column that has two foreign Key relationship . Let me explain in detail.

    Say ,I have three tables. First table A which has column A.ID(Primary Key),A.Name. Second Table B has Column B.ID(Primary Key),B.Name. Third Table C has Column C.ID ,C.Name,C.Detail.
    In C.Detail I have to store data from both table(A.ID & B.ID). So I tried to add two foreign key into the column C.Detail. During Insert Operation in Table B the following error occurs. The error Message is ViceVersa while trying to insert data into the Table A.


    > "The INSERT statement conflicted with the FOREIGN KEY constraint
    > "FK_C_A". The conflict occurred in database "X", table "dbo.A", column
    > A.ID."

    Please, Can any one help us to rectify this problem . We doesnt want to add two columns in table C for two foreign key.

    Hopefully waiting for the reply.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by leemarose View Post
    ...We doesnt want to add two columns in table C for two foreign key.

    Hopefully waiting for the reply.
    If your reluctance to allow two columns for two foreign keys, is very, very important to you, even more important than a working system, then there is no (good, normalised, professional, ...) solution for your problem.

    If you want to store the Name and FirstName of a PERSON. I bet you will create two columns (Name and FirstName) in the table PERSON to store those two values.

    Can you explain why you don't want to follow the same logic when you want to store two foreign keys, that point to two different tables?

    You could use one column, that could refer to both tables, given the PK's in both tables have the same data type. But you will also need another column that would tell you to what table it is referring to (Is C.Detail = 100 referring to the record with ID = 100 in tableA or to the record with ID = 100 in TableB?). But you will not be able to create a FK constraint on your table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    Ok take this idea ,That may help you
    A foreign key can only reference a single primary key, so no. However, you could use a bridge table:

    CustomerA <---- CustomerA_Orders ----> Order
    CustomerB <---- CustomerB_Orders ----> Order

    So Order doesn't even have a foreign key; whether this is desirable, though...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •