Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

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?
0
axessJosh
Asked:
axessJosh
  • 2
2 Solutions
 
sweetfa2Commented:
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
 
sweetfa2Commented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now