Determining Primary Keys

Question

Determining Primary Keys

tymberwyld

SSCertifiable

Points: 7810
More actions
November 7, 2003 at 6:37 am

#160906

Does anyone have some insight on Composite Keys?
Currently, I only use one Primary Key per table, which sometimes concatenates the Parent's Pri-Key (ex. CustomerID + SequenceNo for an OrderID).
Should I continue to concatenate, or is there some kind of advantage to using Composite Keys?
November 10, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

this is going into theory. and there isn't an ultimate answer anyway. you should analyze your table structure and decide then.

remember, the definition of the 3NF is 'all non-keys are determined by the key, the whole key and nothing but the key, so help me Codd!'

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

tymberwyld SSCertifiable Points: 7810 More actions · Answer 2

Ok, maybe you have a Link to Database Normalization rules I can read? I know I've read it before, but I don't have the book anymore...

linto SSC Eights! Points: 851 More actions · Answer 3

I will always not go for composite primary key if that key is going to be a foreign key of another table.

In the case if you have an orderdetail table, what you will do ?? you need PK of Order table for reference.. so in this case, i prefer not to use for composite pk on the order table as this is going to be part of several where clauses..

Linto

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

I choose not to use composite keys as the primary. Life is simpler that way. Less to type when you join, smaller key means better perf, and hopefully the single col pkey will never get changed. Not the book answer, but pragmatic in my view. Dealing with a changing pkey in a trigger sucks.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Ok, maybe you have a Link to Database Normalization rules I can read? I know I've read it before, but I don't have the book anymore...

a good start might be

http://www.sqlservercentral.com/columnists/bkelley/normalization.asp

...and a good book that normal people like us can understand is

'Database Design for Mere Mortals (Second Edition)' by Michael J. Hernandez; Addison-Wesley; ISBN: 0-201-75284-0

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

tymberwyld SSCertifiable Points: 7810 More actions · Answer 6

Thanks for the input guys, this isn't really for me...I just needed proof for someone else =P. I'd actually never, ever use a Composite Key. I always try to think of Unique ways to generate a PriKey by concatenating the Parent Table's Key with something unique in the Child.

You are correct, much eassier, faster, and other developers will love you because you kept it simple =0).

For example, I am working on a database design (not my own) that uses 7 Primary Keys on one table! Can you say OMG!!!?