I could explain why you shouldn’t implement composite PKs in real life databases, I could quote a few articles on the subject (my favorites are Scott Ambler’s writings at Ronin) and point out that eventhough composite primary keys look good and make sense in theory, they make the life of programmers like me, difficult. Why? simply because when you have a composite foreign key, things get complicated, you no longer can use this simple and straighforward constructuction to filter values:
SELECT field1, field2 from ChildTable where FKField in (Select PKfield from ParentTable where fileteringfield=’value’)
If the FK is a composite key, this is two fields instead of one field as FK, the statement:
SELECT field1 from ChildTable where FKField1, FKField2 in (Select PKfield1, PKfield2 from ParentTable where fileteringfield=’value’)
will fail.
I know someone might point out, why not using joins? Sure that would be the only solution, but you would be joining on two fields instead of one…and why consuming more resources on a nested loop for the inner join?