Change Database Information on multiple tables best practice

I have a database that manages our communication with clients.

When I built it, i setup the group ID field to just auto-increment from 1.  So my groupIDs are 1, 2, 3, ... etc.

Now that we've grown, I want to use an ID that is less "Guessable".  I'd like to change it to some random variation of an MD5 field.  Probably take the first 5 characters or something like that.

I can easily change the group IDs and start inserting new from here forward.  

However, my question is about updating the info records that are currently tied to the old client ID.  What would be the best practice for that?  Perhaps create an Archive Client ID field in the table to keep the connection or run an UPDATE command of the field to change ids from old to new?

Ideas?
LVL 2
axessJoshAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sweetfa2Connect With a Mentor Commented:
There is a field called a GUID that is normally used for this purpose.

insert into table (pkfield, …) values (uuid(), …);

Refer to http://stackoverflow.com/questions/5230638/mysql-autoincrement-to-guid for some more details on how to achieve this.
0
 
sweetfa2Connect With a Mentor Commented:
First step = back up.

Create a new table with each PK and a new UUID.

Then, for each table that has the PK run an update on it using the old PK and the relationship table as the new PK.

You will probably have to disable any FK constraints you have whilst you are doing it, or do it otherwise you will get errors.
0
 
axessJoshAuthor Commented:
Since the relationship table is a bit new to me, could you either detail a bit more or point me to an explanation?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.