删除索引是不可能的

The following create statement shows the current structure of one of my MariaDB tables.

以下create语句显示了我的一个MariaDB表的当前结构。

CREATE TABLE `councilor` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `user` BIGINT(20) NOT NULL,
    `council` INT(11) NOT NULL,
    `role` CHAR(50) NOT NULL DEFAULT 'member' COLLATE 'utf8_persian_ci',
    `cDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `uDate` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    `startDate` DATETIME NULL DEFAULT NULL,
    `endDate` DATETIME NULL DEFAULT NULL,
    `responsibility` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_persian_ci',
    `details` TEXT NULL COLLATE 'utf8_persian_ci',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `userId_councilId` (`user`, `council`),
    INDEX `council_user_council_foreign` (`council`)
)
COLLATE='utf8_persian_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=11;

I need to remove the unique index, however trying to delete either of two keys fails.

我需要删除唯一索引,但尝试删除两个键中的任何一个失败。

ALTER TABLE `councilor`
    DROP INDEX `userId_councilId`;

/* SQL Error (1553): Cannot drop index 'userId_councilId': needed in a foreign key constraint */

/ * SQL错误(1553):无法删除索引'userId_councilId':外键约束中需要* /

ALTER TABLE `councilor`
    DROP INDEX `council_user_council_foreign`;

/* SQL Error (1553): Cannot drop index 'council_user_council_foreign': needed in a foreign key constraint */

/ * SQL错误(1553):无法删除索引'council_user_council_foreign':外键约束中需要* /

Removing all table's rows does not help as well. However, if a new table is created by the above script the issue will not occur.

删除所有表的行也没有用。但是,如果上述脚本创建了新表,则不会发生此问题。

3 个解决方案

#1

You have to drop FOREIGN KEY constraint first, then drop index and recreate FOREIGN key.

您必须首先删除FOREIGN KEY约束,然后删除索引并重新创建FOREIGN键。

#2

18:18:46 ALTER TABLE location DROP INDEX unique_name_to_supplier Error Code: 1553. Cannot drop index 'unique_name_to_supplier': needed in a foreign key constraint 0.063 sec

18:18:46 ALTER TABLE位置DROP INDEX unique_name_to_supplier错误代码:1553。无法删除索引'unique_name_to_supplier':外键约束需要0.063秒

This means you are trying to delete index that used for FK.

这意味着您正在尝试删除用于FK的索引。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

MySQL需要外键和引用键上的索引,以便外键检查可以很快并且不需要表扫描。在引用表中,必须有一个索引,其中外键列以相同的顺序列为第一列。如果索引不存在,则会自动在引用表上创建此索引。如果您创建另一个可用于强制执行外键约束的索引,则可以稍后以静默方式删除此索引。如果给定,则使用index_name,如前所述。

http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

So to fix is you need:

所以要解决的是你需要:

1.Get rid of FK

1.取消FK

2.Remove index itself

2.删除索引本身

3.Create FK again

3.再次创建FK

Here are the commands:

以下是命令:

#removing FK
    ALTER TABLE `location`  DROP FOREIGN KEY `FK_SUPPLIER`;

#droping unique Index
    ALTER TABLE `location`  DROP INDEX `unique_name_to_supplier` ;

#creating new FK with proper Index    
    ALTER TABLE `location` 
    ADD INDEX `FK_SUPPLIER_idx` (`supplier_id` ASC);
    ALTER TABLE `location` 
    ADD CONSTRAINT `FK_SUPPLIER`
      FOREIGN KEY (`supplier_id`)
      REFERENCES `supplier` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;

full sql log:

完整的SQL日志:

mysql>
mysql> CREATE TABLE supplier (
    ->  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.38 sec)

mysql> CREATE TABLE location (
    ->  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  name VARCHAR(10) NOT NULL,
    ->  supplier_id INT UNSIGNED NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.32 sec)

mysql> ALTER TABLE location ADD CONSTRAINT FK_SUPPLIER FOREIGN KEY FK_SUPPLIER (supplier_id)
    ->    REFERENCES supplier (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE location ADD UNIQUE INDEX unique_name_to_supplier (supplier_id,name);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table location;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table
                                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| location | CREATE TABLE `location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name_to_supplier` (`supplier_id`,`name`),
  CONSTRAINT `FK_SUPPLIER` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql>
mysql>
mysql>
mysql> ALTER TABLE `location`  DROP FOREIGN KEY `FK_SUPPLIER`;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> ALTER TABLE `location`  DROP INDEX `unique_name_to_supplier` ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> ALTER TABLE `location`
    -> ADD INDEX `FK_SUPPLIER_idx` (`supplier_id` ASC);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `location`
    -> ADD CONSTRAINT `FK_SUPPLIER`
    ->   FOREIGN KEY (`supplier_id`)
    ->   REFERENCES `supplier` (`id`)
    ->   ON DELETE NO ACTION
    ->   ON UPDATE NO ACTION;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table location
    -> ;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table
                                                                                                                                                  |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| location | CREATE TABLE `location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_SUPPLIER_idx` (`supplier_id`),
  CONSTRAINT `FK_SUPPLIER` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

#3

You have to drop the foreign key constraint first, and that

您必须首先删除外键约束

ALTER TABLE mytable DROP FOREIGN KEY [Foreign_key_constraint_name];

and than you can drop the index

而且你可以删除索引