Add auto_increment feature to a primary key

Question

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.

Does this help? https://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint — GetSet, Feb 06 '20 at 17:02
OT note: On some systems table names are case sensitive and `receptor` is not the same as `Receptor`. So please be consistent to avoid problems when you decide to migrate your DB to another system. — Paul Spiegel, Feb 06 '20 at 18:02
Related: [How to drop auto_increment from a mysql table](https://stackoverflow.com/questions/44087283/how-to-drop-auto-increment-from-a-mysql-table) — Paul Spiegel, Feb 10 '20 at 17:25

Paul Spiegel · Accepted Answer · 2020-02-06T17:38:40.040

1

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.

edited Feb 06 '20 at 17:38

answered Feb 06 '20 at 17:33

Paul Spiegel

30,925
5
44
53

score 0 · Answer 2 · answered Feb 06 '20 at 17:24

ALTER TABLE Receptor MODIFY COLUMN ODB_ID INT AUTO_INCREMENT;

I believe that the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY, so if that does not work, try:

ALTER TABLE Receptor MODIFY COLUMN ODB_ID INT AUTO_INCREMENT PRIMARY KEY;

Add auto_increment feature to a primary key

2 Answers2