SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Composite Primary key and Foreign key creation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learntsql
Aged Yak Warrior

India
513 Posts

Posted - 09/26/2013 :  10:39:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 09/26/2013 :  11:15:05  Show Profile  Reply with Quote
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
Aged Yak Warrior

India
513 Posts

Posted - 09/27/2013 :  01:31:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 09/27/2013 :  08:37:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000