SQL Server Performance Forum – Threads Archive
FK refer to part of primary key
Hi Alli have a question concerning Foreign key. one parent table (called D) uses composit key(Recip_ID+Prov_Num). the child table H doesn’t have primary key but Recip_ID and Prov_Num columns exist in child table H. is it possible for me to build a FK (on Recip_ID in table H) that refer to the Recip_ID (part of primary) on parent table? our PM asked DBA to do that. something like: ALTER TABLE H
ADD CONSTRAINT R_2 FOREIGN KEY (Recip_ID) REFERENCES D(Recip_ID) thanks in advance for any help
the foreign key must reference a unique column of the parent table
this could be a PK, unique constraint, probably a unique index so you would need a unique index on Recip_ID in the parent table,
but i don’t think thats true it might be time to find someone who understands database architecture
It seems that you are going to identify parent table by Recip_ID+Prov_Num. In that case child table also shoudl have a both fileds. I think you can add a another column to parent table(Identity filed) naming it as SOMEID. then you should have a SOMEID in child table —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Say your "parent table" has a unique key on 2 columns. You then take 1 column, and want to associate values to the unique values on that column, and call it a "child table". It’s actually the other way around: your new table would be a parent table for your existing table. Either that, or your data model is faulty.
]]>