Re: Sequence Numbers as Primary Keys

From: David Rolfe <drolfe_at_eng>
Date: 1995/10/02
Message-ID: <44pk06$1p_at_engnews2.Eng.Sun.COM>#1/1

 In article <mreagan-2809952336350001_at_mreagan.fast.net>,

>  <mreagan_at_fast.net> wrote:

> >
> >"intelligent" keys will almost always come back to haunt you. Your users
> >will eventually want to modify one (or more) of the columns in your
> >primary key (but we transfered that product to another division so the
> >part "number" has to change!). If this primary key is used as a foreign
> >key in other tables, you have a major mess on your hands: Not only do you
> >have to update the master table, but you must change all of the dependent
> >tables (once you find ALL of them, of course). Depending on the
> >complexity of your table structures, this may not be possible. Or it may
> >have horrible effects on history tables. If the primary key is a system
> >assigned numeric, you will be updating an attribute that is not repeated
> >in any table.

Using a sequence to provide a primary key is all very well but creates its own problems -

1. If every table in your system has a numeric primary key and you are using Sql*Forms you will not be able to make records appear on the screen sorted by a foreign key value, 'cos the foriegn key stored on the table the block is based on is a meaningless sequence number and the 'real' key is on another table. The only may round this in forms is to enter the strange and unpleasant world of ON-SELECT triggers.
2. Using a system generated primary key can be a cop out. Every table *must* have a unique key which is based on its contents. If you don't know what the unique key is then you can't honestly say what the table is keeping track of. Any damn fool can make something unique by tacking a sequence number on to the front of it and saying 'look! it's unique'. Any table which has a system generated key must also have a 'application related' unique key.
3. If everything in the system has a numeric primary key your reports will end up doing large numbers of joins to 'decode' foreign keys into something meaningfull to humans.
4. Systems in which everything has a numeric primary key are utterly incomprehensible to new developers. Instead of being able to see vaguly familer business terms all they have are lots of tables, some of which will consist of nothing but numbers.

Please don't take the above to mean that system generated keys are a bad thing - they can solve a lot of problems. But from my experience I have learnt to use them when I have a genuine need, and not because its fashionable or because some developer claims it'll make his C program run faster.

Below are the circumstances I know of that would justify using a system generated key:

1. Unique key is too big (e,g. 4 columns)
2. Unique key is unstable (business requirement to allow updates).
3. Major storage space problems.

I'm sure there are other reasons - these are the ones that spring to mind right now.

David Rolfe,
SunSoft,
Mountain View,
CA        Received on Mon Oct 02 1995 - 00:00:00 CET