Bill Benson
bensonforums at gmail.com
Sat Dec 27 19:29:56 CST 2014
??Changing a PK means all its relationships have > to be updated which I solved. And that was the challenge in this for me, which I solved - something Jim you would have recognized if you looked through the code or the notes I wrote above the routine. I only did such a project just to see if it could be done. As for why, well, I don't like index id's that are out sequence, particularly in fairly young databases such as the ones I am actively developing. I am a little ocd, I must admit. No one else has to think it is useful, but it there really a cause to poop my party? If you see something flawed in the solution, that would be helpful to me. :) As I wrote in note3 >>> 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. And it was self-fulfilled prophecy on that score. Thanks for using butane lighters and not flame throwers. Different strokes for different folks. On Sat, Dec 27, 2014 at 7:08 PM, Jim Dettman <jimdettman at verizon.net> wrote: > I'm with Charlotte; what's the problem your trying to solve? > > Jim > > Sent from my iPhone > > > On Dec 27, 2014, at 6:44 PM, Charlotte Foust <charlotte.foust at gmail.com> > wrote: > > > > 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 > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >