March 28, 2013 at 10:25 am
Can there be an FK for a single column referencing only one column of a multiple primary key constraint?
eg. one table contains two columns (posid, empid) which are combined into a clustered primary key. A second table contains a column (posid) which I want to reference only the posid portion of the primary key of the first table.
If so, how?
I keep getting error
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'ph_accessory' that match the referencing column list in the foreign key 'fk_posid_phaccessory'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have checked for what the error is warning about, and the problem isn't there.
March 28, 2013 at 10:58 am
You would need to question why you would be doing this. Unless there is a reason for the data to be phsically ordered and to be unique by those two columns, why have them as a clustered index? You are trying to break the referential integrity by only using one column as the FK in the second table.
Create a normal incrementing id for the table with the PK making that the PK (default clustered index) and then a non-clustered index on the two columns for data retrieval. Use the new PK as the FK in the secondary table.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 28, 2013 at 10:58 am
KoldCoffee (3/28/2013)
Can there be an FK for a single column referencing only one column of a multiple primary key constraint?eg. one table contains two columns (posid, empid) which are combined into a clustered primary key. A second table contains a column (posid) which I want to reference only the posid portion of the primary key of the first table.
If so, how?
I keep getting error
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'ph_accessory' that match the referencing column list in the foreign key 'fk_posid_phaccessory'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have checked for what the error is warning about, and the problem isn't there.
When you have a composite key you can't have a foreign key reference only a portion of the primary key in the other table. There are some workarounds but they are kind of messy and generally it is an indication that the ddl needs to be changed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 11:15 am
You can only reference a column (or columns) that have a unique constraint or index.
If the column you want to reference is unique by itself, then you can add a unique constraint or index. However, that begs the question of why you have more than one column in the PK if a single column is unique.
March 28, 2013 at 11:28 am
OK, I have re-examined my design and am doing away with the clustered PK....will introduce a lookup table instead. Thank you for not letting me hurt myself. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply