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.
>
> I'd like to re-number the primary key and then re-set the sequence then
> back to the correct number to increment.
>
> Anyone have any lessons leaned that I need to watch in doing this?
>
> 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

By the way the cache size is how many numbers are stored in memory so that an IO to disk does not have to be done until the cache is exhausted (or flushed). The increment is what determines the number by which the sequence values vary from each other.

And Ed will probably tell you this but the alter table command includes an option to enable/disable constraints. See the SQL manual.  This explnation should be in the Concepts manual section on constraints.

HTH -- Mark D Powell -- Received on Wed Dec 17 2003 - 03:14:58 CET