Sequence Numbers as Primary Keys
Date: 1995/10/03
Message-ID: <44sf2n$aep_at_dingo.cc.uq.oz.au>#1/1
drolfe_at_eng (David Rolfe) wrote:
> 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.
Ever thought of putting an order by clause on the block.
>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.
I don't believe people have said to use system generated keys on all tables. I believe this thread was about concatenated primary keys and performance issues.
>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.
Again use order by an order by clause in the query...
>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.
Refer to the comment against para 2
>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.
Test it and see, if performance is enhanced use it...
>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)
Exactly what this thread was about...
>2. Unique key is unstable (business requirement to allow updates).
Then is not really a suitable primary key...
>3. Major storage space problems.
Now your talking
>I'm sure there are other reasons - these are the ones that spring to mind
>right now.
>David Rolfe,
>SunSoft,
>Mountain View,
>CA
-----------------------------\ooOoo/----------------------------------- Gordon Hooker MACS PCP ,--_|\ 25 Clarke Street, Ripley, Queensland, 4306, Australia / \ gordonh_at_acslink.net.au \_.--._/ mobile: 018883835 phone: 61-7-2889716 V -----------------------------------------------------------------------It is far better to remain silent and appear a fool, than to open ones mouth and remove all doubt... Received on Tue Oct 03 1995 - 00:00:00 CET