I cannot seem to be able to delete primary keys in a table. All references (FKs) have been removed but it still doesn't let me delete it.

What I'm trying to do is: delete old primary keys to add a new one - but keep the old columns and data (just remove the PK attribute).

What is wrong ?

Table:

CREATE TABLE `employee` (
  `User` int(10) unsigned NOT NULL,
  `Company` int(10) unsigned NOT NULL,
  --unrelated boolean fields
  PRIMARY KEY (`User`,`Company`),
  KEY `FK_Employee_Company_idx` (`Company`),
  CONSTRAINT `FK_Employee_Company` FOREIGN KEY (`Company`) REFERENCES `company` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Employee_User` FOREIGN KEY (`User`) REFERENCES `user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Trying to delete:

alter table Employee
drop primary key;

Issue:

Error 1025: Error on rename of '.\DB_NAME#sql-3640_4' to '.\DB_NAME\employee' (errno: 150 "Foreign key constraint is incorrectly formed") SQL Statement: ALTER TABLE DB_NAME.employee DROP PRIMARY KEY

Nothing references this table anymore. I also checked via statements which select from information_schema.key_column_usage but yields no results.

Wasted the last hours on Google but can't seem to figure it out.

And if that would work, adding a new column:

alter table Employee
add column ID int unsigned not null auto_increment primary key;

CodePudding user response：

The index is still needed for the existing FK constraints.

Adding the following index (first) should satisfy that requirement:

CREATE INDEX xxx ON employee (User, Company);

Test case