Solved

Change Database Information on multiple tables best practice

Posted on 2014-01-06
3
80 Views
Last Modified: 2015-12-31
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
Comment
Question by:axessJosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
sweetfa2 earned 235 total points
ID: 39760756
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
 
LVL 17

Assisted Solution

by:sweetfa2
sweetfa2 earned 235 total points
ID: 39760761
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
 
LVL 2

Author Comment

by:axessJosh
ID: 39777533
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question