Re: Sequence Numbers as Primary Keys

ebruce_at_vespucci.iquest.com (Ed Bruce) 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.
 

>And this can come back to haunt you. I'm working on a project where every
>single table uses a sequence generator for it's unique key (this was
>decided before I joined the project). The only problem is as new versions
>of our system is developed our database changes. So we maintain multiple
>versions of the database. Only one small problem - the sequences are
>different in the different versions of the database. Only one other small
>problem - sequences are different in our development database then the
>installed databases. So we are now developing a tool that dumps out the
>database sans sequence numbers and reloads all the data to synchronize
>the primary keys. Just do that a few times every six months.
 

>Ed Bruce

So start the first sequence at one and increment by ten thousand. 6 months later......
Start the 2nd one at two and increment by ten thousand. Never have a duplicate number, and when you run out of sequences.... It was time to rewrite the system anyways :)

Craig Received on Thu Oct 05 1995 - 00:00:00 CET