Add auto_increment feature to a primary key

I have a problem related to sql language. I am trying to add the auto_increment features to a pre-existing row with the primary key attribute. What I get back is :

Error Code: 1833. Cannot change column ‘ODB_ID’: used in a foreign key constraint target_ibfk of table ‘test3.target’.

The statement used for the table was

CREATE TABLE `Receptor` (
  `ODB_ID` int(11) NOT NULL,
  `Name` varchar(10) NOT NULL,
  `Older Name` varchar(10) NOT NULL,
  `Uniprot_ID` varchar(10) NOT NULL,
  `Organism` enum('H','M','R') NOT NULL,
  `Sequence` varchar(1000) NOT NULL,
  PRIMARY KEY (`ODB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

And this table refer to this one:

CREATE TABLE `Target` (
  `Pubchem_ID` int(11) NOT NULL,
  `ODB_ID` int(11) NOT NULL,
  `Receptor` varchar(10) NOT NULL,
  `EC50` decimal(6,3) DEFAULT NULL,
  `Reference_ID` int(11) NOT NULL,
  KEY `Pubchem_ID` (`Pubchem_ID`),
  KEY `ODB_ID` (`ODB_ID`),
  KEY `Reference_ID` (`Reference_ID`),
  CONSTRAINT `target_ibfk_1` FOREIGN KEY (`Pubchem_ID`) REFERENCES `general` (`Pubchem_ID`),
  CONSTRAINT `target_ibfk_2` FOREIGN KEY (`ODB_ID`) REFERENCES `receptor` (`ODB_ID`),
  CONSTRAINT `target_ibfk_3` FOREIGN KEY (`Reference_ID`) REFERENCES `publication` (`Reference_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I have tried with the following commands plus from the MySQL gui:

alter table `Receptor` MODIFY `ODB_ID` int not null auto_increment PRIMARY KEY;

alter table `Receptor` CHANGE `ODB_ID` `ODB_ID` int not null auto_increment PRIMARY KEY;

I viewed another suggestion on the site, but it doesn’t work for me.

Thank you in advance.

Answer

You need to disable FOREIGN_KEY_CHECKS. Then enable it again after the operation. The following works for me:

set session foreign_key_checks = 0;

alter table `Receptor` MODIFY `ODB_ID` int not null auto_increment;

set session foreign_key_checks = 1;

See demo

Note that I also removed PRIMARY KEY after auto_increment, since ODB_ID is already the PRIMARY KEY.

Advertisement

Answer