By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,364 Members | 1,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,364 IT Pros & Developers. It's quick & easy.

Concatenated/composite primary key pros cons?

P: n/a
My client manager likes concatenated/composite primary keys. I don't.

Can anyone forward any arguments pro or con?

Thanks,
Ron
Feb 20 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a

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
Feb 20 '07 #2

P: n/a
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
Feb 20 '07 #3

P: n/a
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

Feb 20 '07 #4

P: n/a

"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

Feb 20 '07 #5

P: n/a
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
Feb 20 '07 #6

P: n/a

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.
Feb 22 '07 #7

P: n/a
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?

Feb 23 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.