Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
learntsql
524 Posts |
 Posted - 2013-09-26 : 10:39:09
|
| Hi All,I have table1 as parent table and Table2 as child table.In parent table i have a composit primary key created on two columns.In child table one of the columns refering this parent table.but i am unable to create a foreign key as it is throwing an error sayingThere are no primary or candidate keys in the referenced table 'table1' that match the referencing column list in the foreign key 'FK__table2__col6__25869641'.Can anyone please guide me for this kind of design what is the approach to be followed?Thanks in advance. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-26 : 11:15:05
|
| You will have to use both columns in the foreign key as well. This makes sense because, if were able to define a foreign key using only one of the columns in the parent table, and if there is more than one row in the parent table with the same value in that column, SQL Server couldn't know which of those rows would the foreign key be referring to. |
 |
|
|
learntsql
524 Posts |
Posted - 2013-09-27 : 01:31:11
|
| Hi James,Thanks for your reply.Is this the better solution or should i create one Auto Identity column in parent table and insert this ID value in child tables.Please suggest this.TIA. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-27 : 08:37:42
|
| It could go either way depending on the details of your problem. I don't know of a deterministic ruleset that one can use to say that it is to be done one way or the other. If the combination of the two columns is what defines uniqueness of a row in the parent table, then there isn't any reason not to have both those columns in the child table as well. But if the two columns are wide, then there is the overhead (and uglyness) of carrying over the two columns into every other table that uses this as a foreign key. In that case you might opt for a surrogate key. But if the two columns are wide, then is there room for optimization/normalization there - i.e., can unique values in each of the columns be stored in its own reference table with a surrogate key and then referred to in the parent table? |
|
|
|
|
|
|
Composite Primary key and Foreign key creation