Re: PRIMARY KEYS - Mailing list pgsql-general

Choosing an artificial key is the ideal, according to everything I've
heard. In one of my database classes, I remember I had a classmate who had
worked with some very large datasets of U.S. citizens, and found that there
were actually duplicate social security numbers assigned to different
people. Not many, and I don't recall whether the first person had died
before the SSN was reused, but it really goes to show that they only to
_guarantee_ a unique primary key is to generate it yourself. Yes, you may
want to put a unique index on your SSN field or other candidate key fields
that ought to be unique.

Integer keys are also faster to compare and sort on, so I would expect
joins between tables to execute faster if the join fields are single
integers, compared to a PK that is a combination of varchar() fields.

Wes Sheldahl



elein <elein@varlena.com>@postgresql.org on 05/21/2003 09:03:09 PM

Please respond to elein@varlena.com

Sent by:    pgsql-general-owner@postgresql.org


To:    Karsten Hilbert <Karsten.Hilbert@gmx.net>,
       pgsql-general@postgresql.org
cc:    elein@varlena.com
Subject:    Re: [GENERAL] PRIMARY KEYS



This is unlike any database theory I've heard of.
Choosing a natural key over an artificial key is
the ideal.  I've heard that a lot.

Sometimes there are several candidate keys to
choose from.  And sometimes the primary keys
are more than one column.

Sometimes I bail out to an artificial key when the
primary key is too long, but it depends very much on how
the table will be accessed and who knows what and
when.

--elein

On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote:
> And - if you agree with database theory - a bad one at that.
> Supposedly primary keys should be void of any meaning bar
> their primary key-ness. I got into the habit of starting
> any but the most simple table like this:
>
> create table (
>     id serial primary key,
>     ...
>
> Never had any trouble with that. Good or bad practice ? Gotta
> decide for yourself.
>
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

--
=============================================================
elein@varlena.com     Database Consulting     www.varlena.com
PostgreSQL General Bits    http:/www.varlena.com/GeneralBits/
   "Free your mind the rest will follow" -- en vogue


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
 message can get through to the mailing list cleanly