It's not possible to remove indexes

Question

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

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 */

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 */

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.

Answer 1

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

This means you are trying to delete index that used for 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.

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

2.Remove index itself

3.Create FK again

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:

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 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 2

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

Answer 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