Renaming a column (usually the primary key) that is used as a foreign key in another table with ALTER TABLE syntax (not the new RENAME COLUMN syntax from 10.5.2) causes a Cannot drop index 'PRIMARY': needed in a foreign key constraint error.

For example, say you have these two tables:

create table `test` (

	`testId` int unsigned not null auto_increment primary key,

	`name` varchar(255) not null

);

create table `test_ref` (

	`id` int unsigned not null auto_increment primary key,

	`refId` int unsigned not null,

	`name` varchar(255) not null

);

And you add a foreign key constraint:

alter table `test_ref`

add constraint `test_ref_id`

foreign key (`refId`)

references `test` (`testId`);

And then try to rename test.testId to test.id with this syntax:

ALTER TABLE test

CHANGE testId id INT UNSIGNED AUTO_INCREMENT NOT NULL;

You will get the error mentioned.

Turning off foreign key checks before and after this alter statement will make it work though, and from what I can tell the references in information_schema.KEY_COLUMN_USAGE is correctly updated, but it does not feel right.

Also, if using the more recent RENAME COLUMN syntax, the change goes through without any errors. The problem is that this syntax is fairly new and likely not implemented in a lot of libraries yet, like doctrine/dbal for instance.