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

Can anyone forward any arguments pro or con?

Thanks,
Ron

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"
<(E-Mail Removed)> wrote:

>My client manager likes concatenated/composite primary keys. I don't.
>
>Can anyone forward any arguments pro or con?
>
>Thanks,
>Ron
>

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

Sorry.. should have posted to SQL group.

"Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in
message news:6A7F632C-4115-4DD0-885B-(E-Mail Removed)...
> 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
>>
>>
>>

"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"
> <(E-Mail Removed)> wrote:
>
> >My client manager likes concatenated/composite primary keys. I don't.
> >
> >Can anyone forward any arguments pro or con?
> >
> >Thanks,
> >Ron
> >
>
>

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

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 <(E-Mail Removed)>
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.
>

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?