Concatenated/composite primary key pros cons?

[Ronald S. Cook]

My client manager likes concatenated/composite primary keys. I don't.

Can anyone forward any arguments pro or con?

Thanks,
Ron

 

[Samuel R. Neff]

Composite keys are only appropriate for xref tables. Major concern is
duplicate data--just think about what you need to do to use the
composite key as a FK in another table. Yuck.

Performance is also an issue. Single int primary keys will provide
best join performance.

Additionally composite keys implies that the key data is descriptive
of the row and is not an arbitrarily assigned value. This is
generally bad as the key should never be updated and any descriptive
data can be updated (even if your program currently disallows it,
there is always a situation where any data field could theoretically
be updated).

HTH,

Sam

 

[Guest]

Ronald,
This is the C# LANGUAGE group you are posting this to. What do
concatenated/primary keys in SQL Server (I presume) have to do with the C#
Language?
Cheers,
Peter

 

[Ronald S. Cook]

Sorry.. should have posted to SQL group.

Ronald,
This is the C# LANGUAGE group you are posting this to. What do
concatenated/primary keys in SQL Server (I presume) have to do with the C#
Language?
Cheers,
Peter

 

[Guest]

Composite keys are only appropriate for xref tables. Major concern is
duplicate data--just think about what you need to do to use the
composite key as a FK in another table. Yuck.

Performance is also an issue. Single int primary keys will provide
best join performance.

Use a unique constraint on an identity column but don't make the identity
column the primary key.

 

[Guest]

Here's my take on it. As you can see it is a hotly debated topic.

http://www.dalepreston.com/Blog/2005/08/identity-crisis.html

 

[Samuel R. Neff]

And why would you do that?!?

Sam


------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

 

[joachim]

I agree with Dale, moreover I think this is a theoretical/taste issue:
if you want to have your database completely normalized,
you could go for the longest possible PK candidate and use a unique
constraint on the set of columns, I find this useful for inserts -
double entries will be rejected automatically. On the other hand
unique ID's require less typing work for join and other operations. In
se ID's are dummy elements, but if they clarify your code and make
your work easier, why not use them?