Renaming primary key column in MYSQL

[pravin battula]

Hi,

I'm getting below error while trying to rename a primary key column.

OperationalError: (OperationalError) (1025, "Error on rename of '.\\test\\#sql-540_8' to '.\\test\\users' (errno: 150)") '\nALTER TABLE users CHANGE COLUMN user_id `Users_Id` INTEGER NOT NULL AUTO_INCREMENT' ()

Below is the code i used to alter the column.

alter_column(users.c.user_id, name='Users_Id')

Please do the needful

Regards

Pravin B

[Ergo]

Hi Pravin,

The problem you are seeing here is probably related to the fact you have some other foreign keys that rely on the user_id column, Mysql will do everything to make your life miserable if you change the name of the column/size of type that has other constraints depend on it - and the error message is not exactly helpful here.

To fix your issue you will have to first drop the other constraints that depend on the column, then rename it, and then recreate constraints again.

[pravin battula]

Hey  Ergo, 

Thanks for the reply.

I wonder what will happen to the performance when trying to drop and recreate the foreign key constraints if the table has thousands of data(lets say 50k)?

Regards

Pravin B

[Ergo]

Unless we are talking millions of rows and table of size 500+mb, nothing happens - it will be very fast,

Normally when you start running alters on your table it will get locked for a while, but unless the table is really big you wont notice anything.

The performance of those operations depends on how much memory your system has and how mysql is configured, so i cant tell you exactly,
but lets say 50k rows is a small dataset.