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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Composite Primary key and Foreign key creation

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 saying
There 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.
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -