CODEWITHSUNDEEP
mysqlsqlinnodb
Kevin Boyd
Kevin Boyd

Reputation: 12379

MySQL, rename all the columns of all tables in a db

I have about 500+ tables in a db and need to rename all the columns of all tables. The database engine is InnoDB. Currently I'm executing a per table 

ALTER TABLE RENAME db.table CHANGE oldID newID INT, CHANGE oldDESC newDESC VARCHAR(50);

And this is failing(errno: 150) at certain tables since they have foreign key constrains referencing to other tables.

Is there a smarter way of doing this and avoiding those dreaded errors?

Upvotes: 1

Views: 296

Answers (1)

Dan Bizdadea
Dan Bizdadea

Reputation: 1302

You can try disable foreign keys checks while do the update, then enable them again

EDIT: maybe this can help

START TRANSACTION;  
SET foreign_key_checks = 0; -- off

.. do stuff here    

SET foreign_key_chekcs = 1; -- on.
COMMIT;

Upvotes: 1

Related Questions