[AccessD] Resequence All Autonum Fields - a tentative approach

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
>


More information about the AccessD mailing list