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
Please, Can any one help us to rectify this problem . We doesnt want to add two columns in table C for two foreign key.
...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
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