Thursday, September 08, 2005

Why there shouldn't be primary keys with business meaning...

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?

1 Comments:

Anonymous Anonymous said...

Yahoo Gives Up Reporter's E-Mail
Yahoo had to comply with a demand by Chinese authorities to provide information about a personal e-mail of a journalist who was later convicted under state secrecy laws and sentenced to 10 years in prison, the ...
Love your blog ! I'm bookmarking you!

I have a make money site/blog. It touches on make money related stuff.

Check it out if you get time :-)

8:44 PM  

Post a Comment

<< Home