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
------------------------------------------------------------
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.

On Tue, 20 Feb 2007 11:12:51 -0700, "Ronald S. Cook"
<rc***@westinis.comwrote:

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

Can anyone forward any arguments pro or con?

Thanks,
Ron

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Ronald S. Cook" wrote:

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

Can anyone forward any arguments pro or con?

Thanks,
Ron

[Ronald S. Cook]

Sorry.. should have posted to SQL group.

"Peter Bromberg [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:6A**********************************@microsof t.com...

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

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Ronald S. Cook" wrote:

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

Can anyone forward any arguments pro or con?

Thanks,
Ron

[=?Utf-8?B?RGFsZQ==?=]

"Samuel R. Neff" wrote:

>
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.

>
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
------------------------------------------------------------
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.

On Tue, 20 Feb 2007 11:12:51 -0700, "Ronald S. Cook"
<rc***@westinis.comwrote:

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

Can anyone forward any arguments pro or con?

Thanks,
Ron

[=?Utf-8?B?RGFsZQ==?=]

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

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

--
Dale Preston
MCAD C#
MCSE, MCDBA
"Ronald S. Cook" wrote:

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

Can anyone forward any arguments pro or con?

Thanks,
Ron

[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.


On Tue, 20 Feb 2007 14:50:35 -0800, Dale <da******@nospam.nospam>
wrote:

>
"Samuel R. Neff" wrote:

>>
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.

[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?