CODEWITHSUNDEEP
mysqlmariadb
Brilliand
Brilliand

Reputation: 13714

MySQL error "Cannot drop index" when trying to ADD INDEX

I'm running this query:

ALTER TABLE `loan`
ADD INDEX `Customer ID_Bank Account` (`Customer ID`, `Bank Account`);

and getting this error:

Error in query (1553): Cannot drop index 'Customer ID': needed in a foreign key constraint

I know very well why the index on "Customer ID" cannot be deleted; it's needed in several foreign keys. The question is, why would an ADD INDEX statement EVER try to drop an index?

(And how can I get it to stop trying to drop that index while I'm trying to add the new one?)

My MySQL version string is "5.5.60-MariaDB".

Upvotes: 0

Views: 5069

Answers (2)

Brilliand
Brilliand

Reputation: 13714

According to this MariaDB bug report, when MySQL creates an index as part of creating a foreign key, it marks that index to be automatically deleted later should it be made redundant by another index created later.

Ideally this deletion of the "redundant" index would never fail, but apparently MySQL has a bug here. Fortunately, there's a workaround:

ALTER TABLE `loan`
DROP INDEX `Customer ID`,
ADD INDEX `Customer ID` (`Customer ID`);

Drop and recreate the offending index in a single statement. So long as the index name is exactly the same before and after, MySQL doesn't complain about the index being dropped; and the explicit create on the index causes MySQL to remove the "automatically delete this index" indicator.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48865

The messaging of MySQL is usually misleading to me.

In relational databases foreign keys can only point to:

  • Primary keys, or
  • Unique Constraints. That's it.

In this case you are not talking about the primary key, so what you need is a unique constraint (that may be called UNIQUE INDEX or UNIQUE KEY in MySQL lingo). The word index is usually used to deal with fast data access, but not with referential integrity.

Note: Some indexes can be used to ensure uniqueness rules, that SQL constraints cannot deal with, particularly partial unique indexes. But that's another story.

You can create the unique constraint as:

alter table loan
add constraint uq1 unique (`Customer ID`, `Bank Account`);

A full running example could be:

create table loan (
  id int primary key not null,
  `Customer ID` int,
  `Bank Account` int
);

alter table loan
add constraint uq1 unique (`Customer ID`, `Bank Account`);

create table item (cid int, acc int, val int,
  constraint fk1 foreign key (cid, acc) 
    references loan (`Customer ID`, `Bank Account`)
);

insert into loan (id, `Customer ID`, `Bank Account`) values (1, 20, 30); -- succeeds
insert into loan (id, `Customer ID`, `Bank Account`) values (2, 21, 31); -- succeeds
insert into loan (id, `Customer ID`, `Bank Account`) values (3, 20, 30); -- fails!

See running example at DB Fiddle.

Upvotes: 0

Related Questions