Jim Dettman
jimdettman at verizon.net
Sat Dec 27 20:31:00 CST 2014
I'm not trying to rain on anyone's parade. All I asked was the question why thinking I wasn't understanding something. Well now that you made the why clear, well there's really not much else to say. Jim Sent from my iPhone > On Dec 27, 2014, at 8:29 PM, Bill Benson <bensonforums at gmail.com> wrote: > > ??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 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com