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_indexwith columns (company_id,line1) -
Now, try to drop the index
address_indexwith 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;
