Re: re-numbering pimary-key

Michael Hill <hillmw_at_ram.lmtas.lmco.com> wrote in message news:<3FDE299C.9C38C69E_at_ram.lmtas.lmco.com>...
> I have a series of tables that have primary-key / foreign key
> relationships where the primary key was being incremented in error by 20
> instead of by 1. The default cache value was set to 20. It has since
> been re-set to 1.

read this in the same tone of voice that Morpheus said to Neo: "you think that's air you're breathing?" 8^)
you think this will keep your primary key values in sequence without gaps?

>
> I'd like to re-number the primary key and then re-set the sequence then
> back to the correct number to increment.
>

Why? is there a business reason, or do you just prefer to be neat?

> Anyone have any lessons leaned that I need to watch in doing this?

Let's see you either disable all foreign key constraints involved while doing this or dump your data to files or temp tables, empty your target tables and reload, forcing the primary and foreign key values to be what you want.

>
> Example of proposed numbering change:
> If I have 1,4,8,12,18
>
> I would change:
> 18 to 99,
> 12, to 98.
>
> Then change
> 4 to 2,
> 8 to 3,
> 98 to 4,
> 99 to 5.
>
> Then change the next sequence number to 6.
>
> Anyone see any problem with this?
>
> Mike

Why the two-phase approach? (12->98->4) why not just renumber lowest to highest? there is no overlap involved, just do one PK then all it's child FKs. It's a simple algorithm.

Be I really cannot emphasize enough that you DON'T NEED to do this, unless there is a real business reason.

and a final comment: This really should be posted in comp.databases.oracle.misc

the comp.databases.oracle group is outdated and being phased out.

HTH,
  Ed Prochak

• there are no stupid questions.