Solved

Change Database Information on multiple tables best practice

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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