Re: Primary Key of type string ?

Walt (walt_askier_at_SHOESyahoo.com) wrote:
: sybrandb_at_hccnet.nl wrote:
: > On Thu, 31 May 2007 13:41:01 +0200, Robert Klemme wrote:
: >
: >> And I thought the standard choice would be the key dictated by business
: >> requirements...
: >
: > Question: do you like to have composite, possible lengthy foreign keys
: > in all of your tables?
: > One usually ends up with a surrogate key.
: > And NO those shouldn't be varchar2(31)
: > (Regrettably this is a real world example in a commercial
: > application).
: >

: I think my favorite example is a table with a 6 field primary key, one
: of which is a timestamp. And yes, there are foreign keys that reference it.

: No, I didn't design it. I'm not going to embarrass the software vendor,
: but everyone here has heard of them.

On the other hand, when a query filters on parts of the primary key then possibly only the index must be read, not the table, since the required values are already in the index. Same is true for displaying columns that are in a key, and at various other times as well.

e.g.

	select max(date_is_in_pk),col1_of_pk,col2_of_pk
	from my_table
	group by col1_of_pk,col2_of_pk

The table will likely not be read at all, just the index, which is smaller and already grouped = much less work = faster.

There are pros and cons for surrogate keys and natural keys, and also pros and cons for being consistent in your use of one approach or the other.

$0.10 Received on Fri Jun 01 2007 - 15:31:41 CDT