MySQL #1553 – Cannot drop index

Mysql #1553 – Cannot drop index

how to re-produce?

Let's create tables company and

company.sql

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

company_address.sql

CREATE TABLE `company_address` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `line1` varchar(45) NOT NULL,
  `line2` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `postal_code` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `address_index` (`company_id`,`line1`),
  CONSTRAINT `company_id_fk` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • In above table, we have index address_index with columns (company_id,line1)

  • Now, try to drop the index address_index with below SQL

drop index address_index on company_address;
  • It will raise an error Cannot drop index 'address_index': needed in a foreign key constraint with error code 1553

why MySQL raises an error #1553?

  • A foreign key always requires an index.

how to fix MySQL error #1553?

  • Let's say foreign key didn't have an index then if a new record is inserted then the foreign key constraint would require a full table scan on referenced table.
  • It takes more time it means lower performance
  • So, when creating a foreign key, the database checks if an index exists. If not an index will be created. By default, it will have the same name as the constraint.
  • When there is only one index that can be used for the foreign key, it can't be dropped.
  • If you really wan't to drop it, you either have to drop the foreign key constraint or to create another index for it first.

For above example table company_address let's remove the index address_index

drop index address_index on company_address;

It will throw below error

Error Code: 1553. Cannot drop index 'address_index': needed in a foreign key constraint

so, how to fix it?

we can fix it by adding a new index for foreign key company_id let's name the index as company_id_idx and column company_id after that we can drop the existing column.

  • step1: add new index company_id_idx
ALTER TABLE company_address 
ADD INDEX `company_id_idx` (`company_id` ASC);
  • step2: remove existing index
ALTER TABLE company_address 
DROP INDEX address_index;

References