MySQL - Massive structural changes
I'm working with a database that has links in almost every table to the "user" table to keep track of things like who last created or updated a record. Unfortunately, the primary key of the user table is "username" and not an auto-incrementing identifier. We have frequent situations where the username needs to be changed due to typos or changes in the user profile, and this really takes a long time in the current project.
I want to change the structure to use auto-incremented id, but I can't find an easy way to do it. In SqlServer, I could use a combination of link, cursor, and structural change introspection to do this effortlessly. But from what I've read about MySQL, I (1) can only use loops from ININ in a stored procedure and (2) can only modify a structure from an OUTSIDE stored procedure.
The only option I found was a rather tedious task (1) Create a temporary table to look up username references:
CREATE TEMPORARY TABLE IF NOT EXISTS username_refs as (select table_name, column_name, constraint_name, referenced_table_name, referenced_column_name from information_schema.key_column_usage where REFERENCED_TABLE_NAME = 'user' and referenced_column_name = 'username');
(2) Create a set of SQL statements for each task. For example, this creates a "shadow" column in each table that references the user's name:
select CONCAT('ALTER TABLE `', table_name, '` ADD COLUMN `', column_name,'_2` bigint(20) ;') from username_refs;
This results in hundreds of sql lines. Is there an easier way to do this that I'm just missing?
source to share
Some people prefer to write a script in an external language to iterate over tables and format the ALTER TABLE statements they want. For example, regardless of your preference for Perl, Ruby, PHP, Python, etc. Each scripting language supports an API for accessing MySQL.
What you are doing is good too. You can use SQL to generate a list of ALTER statements, save the output of that file, and then run the file as a SQL script.
By the way, you don't necessarily need a separate autoincrement key. You define your foreign keys on
to enable the UP UPDATE CASCADE. See "Reference Actions" at https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
source to share