Charlotte Foust
charlotte.foust at gmail.com
Sat Dec 27 17:44:26 CST 2014
Sorry, but why do you even want to do this? Autonumbers aren't necessarily sequential. So what? If you need sequential add a surrogate key and make it sequential. Changing a PK means all its relationships have to be updated Charlotte On Dec 27, 2014 12:45 PM, "Bill Benson" <bensonforums at gmail.com> wrote: > On Sat, Dec 27, 2014 at 3:23 PM, Bill Benson <bensonforums at gmail.com> > wrote: > > > I have written this routine to autonumber field in the table. > > If someone wants to kick its tires and see if it handles their complex > > database, great - but I suggest making a backup (perhaps more than one!). > > > > > > https://drive.google.com/file/d/0B-Klb4AlrC9JaExTV1p3d2JBSXc/view?usp=sharing > > > > Order of steps: > > 1) checks the max PK value, make sure there is room to add all records > > again without hitting > > Const HIGHEST_POSSIBLE_VALUE = 2147483647 > > > > 2) Preserve indexes in a table, then delete them > > > > 3) Duplicate records so that there are equivalents in a higher range > > > > 4) Swap all values appearing in related tables that reference this > table's > > PK in their foreign key > > I am not sure if I handle multi-column FK references appropriately. > > > > 5) Delete the predecessor records from the table > > > > 6) Seeds in HIGHEST_POSSIBLE_VALUE = 2147483647 to the table; seed in 0 > > to the table; > > > > 7) duplicates all records again, reusing the range 1, 2, 3 ... for PK > > > > 8) Swap all values appearing in related tables that reference this > table's > > PK in their foreign key > > I am not sure if I handle multi-column FK references appropriately. > > > > 9) Remove the no longer needed higher PK range of duplicate records > > > > 10) Replace indexes > > > > Note1: The routine only works on Fields / Foreign Keys that are long > > integer, and are autoincrementing (it tests). > > > > Note2: No doubt others have created even more robust routines, this was > my > > 1st attempt. > > > > Note3: I will get flamed as creating something that serves no legitimate > > purpose in the database. Oh well, everyone is entitled to their own set > of > > principles. > > > > Note4: If the routine pauses with errors in the midst - that is for > > debugging purposes; don't count on your database maintaining state... I > > would fix the code and start again from backup. > > > > Note5: There will be bypassed errors when indexes are stored and > > re-created for those items which are bound up in relationships. > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >