Solved

Change Database Information on multiple tables best practice

Posted on 2014-01-06
3
71 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger usage 2 75
Sql query on a varchar that is numeric. 8 44
update joined tables 2 55
How to send multiple emails at the same time in PHP 12 72
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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