Re: Sequence Numbers as Primary Keys

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 Received on Sun Oct 01 1995 - 00:00:00 CET