1553 ошибка mysql

A deeper understanding on the matter could be helpful not just for finding a workaround to the error in question but also for better designing our tables and indexes. Some of the existing answers are good, but others are incomplete or clearly wrong, and that could be dangerous for hasty readers.

Important initial notes

• The question is about a UNIQUE index, but that’s of little importance here. What matters is that the index of interest is compound (i.e. it consists of 2+ columns).
• In MySQL KEY is (in this context at least) a synonym to INDEX.

The problem

In MySQL an index is required for every foreign key constraint (FKC). Not all DBMSs have such a requirement, but in practice, if you don’t have such an index, some operations, such as deleting a record in a table that is referenced from other tables might suffer from terrible performance if those referencing tables are big enough. That’s why with some RDBMs like MySQL, it’s been a deliberate design decision to make such indexes required.

When we create a FKC, normally, MySQL automatically creates a new index with the same column(s) as the FK (remember, primary and foreign keys could consist of 2+ columns, but in OP’s example they don’t). However, if there already exists an index that could be used for the FKC, MySQL will not create another index.

In OP’s example, if we don’t count the primary key index, we have 3 indexes: AID,BID,CID. The first one is compound, the others are not. Note that the name of the unique index might be a bit misleading, as it’s the same as the name of the first column. I always recommend giving explicit names to compound indexes, to avoid potential confusion. So let’s imagine the name of the UNIQUE KEY is ABC and see what indexes the table would have at the starting point:

UNIQUE KEY `ABC` (`AID`,`BID`,`CID`),
       KEY `BID` (`BID`),
       KEY `CID` (`CID`),

The key ABC can be used for the FKC on AID, because AID is the first column in it. That’s why MySQL decided not to create an extra index on AID only. But dropping an index that is internally used by MySQL to satisfy the requirement of always having an index, would be a no-no. Hence the error.

The solution

We should first ask ourselves: do we want to have an explicit dedicated index for AID only, or do we want to continue using a compound index that we are having anyway. Both would be valid options, depending on our needs. If we want the best possible performance when quering on AID only, having a separate index might be slightly more efficient, but that comes with the cost of having one more index (more storage, slower updates, etc.). More often than not, the pros of using the compound index outweigh the cons, but when performance is critical, it might be useful to at least be aware that there is an option to have a smaller dedicated index.

Option 1: Having a (permanent) dedicated index for the FKC

First, we create the dedicated index for the FKC:

CREATE INDEX IDX_AID ON mytable(AID);

Note that I’ve used IDX_AID as a name here, but if we preferred to stick to the way MySQL names such indexes, AID could also be used if available at this point. In my example with ABC it would be, but in OP’s case it wouldn’t (although with RENAME INDEX that could easily be solved).

Then we re-create the unique index with the new column (let’s call it NEW_COL and let’s say we want it to be somewhere in the middle /remember, column order in indexes does matter/):

DROP INDEX ABC ON mytable;
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);

Now with SHOW CREATE TABLE mytable we should be having:

UNIQUE KEY `IDX_ABNC` (`AID`,`BID`,`NEW_COL`,`CID`),
       KEY `IDX_AID`  (`AID`),
       KEY `BID`      (`BID`),
       KEY `CID`      (`CID`),

Option 2: No dedicated index for the FKC

We essentially need a workaround, to avoid the temporary inconsistent state.

Method 1: Temporary index on the FKC (preferred)

This is similar to Option 1 but we just drop the index at the end:

CREATE INDEX IDX_TMP ON mytable(AID);
DROP INDEX IDX_ABC ON mytable;  -- here we have IDX_TMP, so no problem
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
DROP INDEX IDX_TMP ON mytable;  -- not needed anymore

Method 2: Drop FKC and then recreate it

Essentially, the idea is to first drop the «guard» (i.e. the FKC) temporarily, and recreate it at the end.

ALTER TABLE mytable DROP FOREIGN KEY <FKC-name>;    
DROP INDEX IDX_ABC ON mytable;  -- no "guard" here to stop us
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
ALTER TABLE mytable ADD CONSTRAINT <FKC-name> FOREIGN KEY (AID) REFERENCES mytable(AID) ON DELETE CASCADE;

You can find the <FKC-name> from SHOW CREATE TABLE mytable.

Recommendation

I think Method 1 is preferable, for the following reasons:

• A bit simpler (no need to look up for the exact FKC name).
• Dropping the FKC, even if for a very short time, opens the window for someone inserting an invalid value.
• There is a potential risk to mix something up when recreating the FKC. For example, you might forget to add an important attribute (such as ON DELETE CASCADE). Or, to accidentally put a wrong one.
• If, for some reason, we forgot or failed to execute the last step, with Method 1 no harm is done, whereas with Method 2 we’ve left the DB in a vulnerable state.

A deeper understanding on the matter could be helpful not just for finding a workaround to the error in question but also for better designing our tables and indexes. Some of the existing answers are good, but others are incomplete or clearly wrong, and that could be dangerous for hasty readers.

Important initial notes

• The question is about a UNIQUE index, but that’s of little importance here. What matters is that the index of interest is compound (i.e. it consists of 2+ columns).
• In MySQL KEY is (in this context at least) a synonym to INDEX.

The problem

In MySQL an index is required for every foreign key constraint (FKC). Not all DBMSs have such a requirement, but in practice, if you don’t have such an index, some operations, such as deleting a record in a table that is referenced from other tables might suffer from terrible performance if those referencing tables are big enough. That’s why with some RDBMs like MySQL, it’s been a deliberate design decision to make such indexes required.

When we create a FKC, normally, MySQL automatically creates a new index with the same column(s) as the FK (remember, primary and foreign keys could consist of 2+ columns, but in OP’s example they don’t). However, if there already exists an index that could be used for the FKC, MySQL will not create another index.

In OP’s example, if we don’t count the primary key index, we have 3 indexes: AID,BID,CID. The first one is compound, the others are not. Note that the name of the unique index might be a bit misleading, as it’s the same as the name of the first column. I always recommend giving explicit names to compound indexes, to avoid potential confusion. So let’s imagine the name of the UNIQUE KEY is ABC and see what indexes the table would have at the starting point:

UNIQUE KEY `ABC` (`AID`,`BID`,`CID`),
       KEY `BID` (`BID`),
       KEY `CID` (`CID`),

The key ABC can be used for the FKC on AID, because AID is the first column in it. That’s why MySQL decided not to create an extra index on AID only. But dropping an index that is internally used by MySQL to satisfy the requirement of always having an index, would be a no-no. Hence the error.

The solution

We should first ask ourselves: do we want to have an explicit dedicated index for AID only, or do we want to continue using a compound index that we are having anyway. Both would be valid options, depending on our needs. If we want the best possible performance when quering on AID only, having a separate index might be slightly more efficient, but that comes with the cost of having one more index (more storage, slower updates, etc.). More often than not, the pros of using the compound index outweigh the cons, but when performance is critical, it might be useful to at least be aware that there is an option to have a smaller dedicated index.

Option 1: Having a (permanent) dedicated index for the FKC

First, we create the dedicated index for the FKC:

CREATE INDEX IDX_AID ON mytable(AID);

Note that I’ve used IDX_AID as a name here, but if we preferred to stick to the way MySQL names such indexes, AID could also be used if available at this point. In my example with ABC it would be, but in OP’s case it wouldn’t (although with RENAME INDEX that could easily be solved).

Then we re-create the unique index with the new column (let’s call it NEW_COL and let’s say we want it to be somewhere in the middle /remember, column order in indexes does matter/):

DROP INDEX ABC ON mytable;
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);

Now with SHOW CREATE TABLE mytable we should be having:

UNIQUE KEY `IDX_ABNC` (`AID`,`BID`,`NEW_COL`,`CID`),
       KEY `IDX_AID`  (`AID`),
       KEY `BID`      (`BID`),
       KEY `CID`      (`CID`),

Option 2: No dedicated index for the FKC

We essentially need a workaround, to avoid the temporary inconsistent state.

Method 1: Temporary index on the FKC (preferred)

This is similar to Option 1 but we just drop the index at the end:

CREATE INDEX IDX_TMP ON mytable(AID);
DROP INDEX IDX_ABC ON mytable;  -- here we have IDX_TMP, so no problem
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
DROP INDEX IDX_TMP ON mytable;  -- not needed anymore

Method 2: Drop FKC and then recreate it

Essentially, the idea is to first drop the «guard» (i.e. the FKC) temporarily, and recreate it at the end.

ALTER TABLE mytable DROP FOREIGN KEY <FKC-name>;    
DROP INDEX IDX_ABC ON mytable;  -- no "guard" here to stop us
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
ALTER TABLE mytable ADD CONSTRAINT <FKC-name> FOREIGN KEY (AID) REFERENCES mytable(AID) ON DELETE CASCADE;

You can find the <FKC-name> from SHOW CREATE TABLE mytable.

Recommendation

I think Method 1 is preferable, for the following reasons:

• A bit simpler (no need to look up for the exact FKC name).
• Dropping the FKC, even if for a very short time, opens the window for someone inserting an invalid value.
• There is a potential risk to mix something up when recreating the FKC. For example, you might forget to add an important attribute (such as ON DELETE CASCADE). Or, to accidentally put a wrong one.
• If, for some reason, we forgot or failed to execute the last step, with Method 1 no harm is done, whereas with Method 2 we’ve left the DB in a vulnerable state.

I have a table with two foreign key constraints as below:

mysql> SHOW CREATE TABLE `user`;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int(11) unsigned DEFAULT NULL,
  `town_id` int(11) unsigned DEFAULT NULL,
  `fullname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_G38T6P7EKUXYWH1` (`region_id`),
  KEY `FK_J8VWK0ZN7FD2QX4` (`town_id`),
  CONSTRAINT `FK_G38T6P7EKUXYWH1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_J8VWK0ZN7FD2QX4` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I can’t drop a foreign key column although I disable FOREIGN_KEY_CHECKS.

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1553 - Cannot drop index 'FK_G38T6P7EKUXYWH1': needed in a foreign key constraint

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1828 - Cannot drop column 'region_id': needed in a foreign key constraint 'FK_G38T6P7EKUXYWH1'

Вопрос:

CREATE TABLE mytable_a (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_b (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_c (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


CREATE TABLE `mytable` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`AID` tinyint(5) NOT NULL,
`BID` tinyint(5) NOT NULL,
`CID` tinyint(5) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `AID` (`AID`,`BID`,`CID`),
KEY `BID` (`BID`),
KEY `CID` (`CID`),
CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`AID`) REFERENCES `mytable_a` (`ID`) ON DELETE CASCADE,
CONSTRAINT `mytable_ibfk_2` FOREIGN KEY (`BID`) REFERENCES `mytable_b` (`ID`) ON DELETE CASCADE,
CONSTRAINT `mytable_ibfk_3` FOREIGN KEY (`CID`) REFERENCES `mytable_c` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB;




mysql> ALTER TABLE mytable DROP INDEX AID;
ERROR 1553 (HY000): Cannot drop index 'AID': needed in a foreign key constraint

Лучший ответ:

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ; 

Ответ №1

SHOW CREATE TABLE  <Table Name>

CONSTRAINT 'FOREIGN_KEY_NAME' FOREIGN KEY ('FOREIGN_KEY_COLUMN') REFERENCES 'FOREIGN_KEY_TABLE' ('id'),

ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign key name>

Ответ №2

CREATE TABLE mytable_d (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


ALTER TABLE mytable
ADD COLUMN DID tinyint(5) NOT NULL,
ADD CONSTRAINT mytable_ibfk_4
FOREIGN KEY (DID)
REFERENCES mytable_d (ID) ON DELETE CASCADE;

> OK.

ALTER TABLE mytable
DROP KEY AID ;

ALTER TABLE mytable
DROP KEY AID ,
ADD UNIQUE KEY AID (AID, BID, CID, DID);

Ответ №3

CREATE INDEX aid_index ON mytable (AID);

ALTER TABLE mytable DROP INDEX AID;

Ответ №4

Ответ №5

set FOREIGN_KEY_CHECKS=1;

ALTER TABLE mytable DROP INDEX AID;

set FOREIGN_KEY_CHECKS=0;