SQL Server - Multiple foreign key in single column

Asked By Leema on 11-Oct-11 05:02 AM

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 seperate the columns in table C for two foreign key.

    Hopefully waiting for the reply.

Anoop S replied to Leema on 11-Oct-11 05:04 AM

No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?

You would at least need a field that tells what kind of user it is, or two separate foreign keys.

You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.

Web Star replied to Leema on 11-Oct-11 05:08 AM
Can't be possible, you can put multiple foreign key in single table but you can't map two foreign key with one column in a table you should map two different column in the same table