Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!cyclone2.usenetserver.com!news.webusenet.com!newsfeed1.cidera.com!Cidera!feedme.ziplink.net!petpeeve.ziplink.net!not-for-mail From: "David Cressey" Newsgroups: comp.databases.theory References: <6278687.0211201544.4578fb26@posting.google.com> Subject: Re: What is the use of a primary key? Lines: 34 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Thu, 21 Nov 2002 18:33:06 GMT NNTP-Posting-Host: 206.15.134.233 X-Trace: petpeeve.ziplink.net 1037903586 206.15.134.233 (Thu, 21 Nov 2002 13:33:06 EST) NNTP-Posting-Date: Thu, 21 Nov 2002 13:33:06 EST Xref: newsfeed1.easynews.com comp.databases.theory:23825 X-Received-Date: Thu, 21 Nov 2002 11:32:50 MST (news.easynews.com) > Why is it necessary to consider a primary key instead of > simply defining a set of candidate keys?. > My answer is about practice, rather than theory. In practice it's usually better to pick one of the candidate keys, and declare that all "foreign key references" will be made to this candidate key rather than to any of the other candidate keys. This candidate gets "elected" to be the primary key. Two benefits of doing this are: it's easier to remember the join conditions when you combine the table with other tables. If all foreign keys reference the primary key, you don't have to stumble around figuring out which candidate key to use in the join condition... it's always the primary key. Second, it's easier to come up with a good indexing strategy. For most DBMS products, a sorted index on both the foreign key and the primary key will result in a "merge join" of some kind. For large scale joins, this can be very fast. If different candidate keys were used in different joins, it would take more indexing to get the same benefit. In fact, in Oracle, when you declare a PRIMARY KEY, you get an index on that key, like it or not. "In theory, there is no difference between theory and practice. In practice, there is." -author unknown-