Re: What is the use of a primary key?

> 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- Received on Thu Nov 21 2002 - 19:33:06 CET