473,841 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Concatenated/composite primary key pros cons?

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

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
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
Sorry.. should have posted to SQL group.

"Peter Bromberg [C# MVP]" <pb*******@yaho o.yabbadabbadoo .comwrote in
message news:6A******** *************** ***********@mic rosoft.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

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

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******@nospa m.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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

112
10377
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please, share your experience in using IDENTITY as PK .
5
7646
by: Fred | last post by:
Not much expertise on XSLT and trying to understand it's uses when creating apps in VS.NET? If I wanted flexibility on the UI (View aspect of M.V.C.): - How does it compare with creating business components that can be consumed by WebForms, WinForms, mobile devices, etc? Is it even fair to compare the such technologies? - How about for cases when you need to display dynamic elements on the form/grid (as compared to knowing data elements...
2
2830
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the code in stored procedures (im advocating encryption of them). When deploying an application however stored procedure seem to add another level of complexity to installation. In future we also plan to have an basic ASP app with some of the...
18
12673
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many relations with the table containing no primary key. Do I just create two primary keys on the table that does not contain any primary key for this to become a composite primary key? Thank you in advance! Still a newbie,
4
4060
by: Ismail Rajput | last post by:
Is there any option we can use Composite DataKeyField in the DataList and DataGrid?
4
4032
by: Sally Sally | last post by:
Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for...
3
4232
by: Andrea | last post by:
Hello everyone, I'd like to know which are the main pros and cons of using XML implementation in business organizations. >From a technical perspective, I find XML powerful, but looks like it is being pushed more from technical people than from businessmen... So, some questions: 1. Pros and cons
115
6293
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this:
2
3513
by: lfhenry | last post by:
Hi All, I am about to make some changes to a process we have. The new process will have a new table who's job it is to store temporarily data such as customer, clerkno,productno, storeno , amount, time. On a new request from a front-end i will select against the primary keys to ensure no other request for the same data has been made. This is then sent to a backend system asynchronously if there is no match. I will otherwise send a...
0
9706
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10927
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10667
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9446
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5691
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5880
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.