How to create a foreign key referencing one column in a composite primary key

Question

I have two tables Catalog and CatalogIndex

catalog has the following columns

DN
PID
PURL
Desc

where the two columns DN, PID are part of the composite primary key for this table.

CatalogIndex has the following columns

PID
PItem
PVal

PID and PItem are part of the composite primary key for CatalogIndex table.

I want to add a a foriegn key to CatalogIndex on PID column that refernces PID in Catalog table.

I am using SQL Server 2008

Thank you

Answer 1

You cannot reference only parts of a compound primary key (one of the many drawbacks of compound PK - it gets really messy to reference them).

You need to reference the key, the whole key and nothing but the key (so help you Codd :-) ).

The only other option would be to create a new, UNIQUE INDEX on those columns you wish to reference (if they are indeed unique on their own) and then use that unique index to reference that subset of columns.

Answer 2

You might need a more normalized schema, with a separate table that has a PK on PID column. Your request is a smell that indicates imperfect database design.