How to drop multiple-column indexes on MySql?

Question

I have a unique key for 2 columns (module_id, order) that I want to drop it. This is the output of SHOW INDEX FROM slides;

+--------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| slides |          0 | PRIMARY                       |            1 | id          | A         |         114 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| slides |          0 | slides_module_id_order_unique |            1 | module_id   | A         |          15 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| slides |          0 | slides_module_id_order_unique |            2 | order       | A         |         114 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

When I run DROP INDEX slides_module_id_order_unique on slides; It fails with

ERROR 1553 (HY000): Cannot drop index 'slides_module_id_order_unique': needed in a foreign key constraint

And when I run ALTER TABLE slides DROP INDEX slides_module_id_order_unique; also fails with same error

@shadow Your reference answer is not working when you have multiple-column indexes. I already mentioned that in my question — A. Khaled, Aug 14 '23 at 12:25
The answers in the duplicate question tell you to drop the foreign key first, not to drop the index. You have not read the answers properly. — Shadow, Aug 14 '23 at 12:27
mysql> alter table slides DROP FOREIGN key slides_module_id_order_unique; ERROR 1091 (42000): Can't DROP 'slides_module_id_order_unique'; check that column/key exists — A. Khaled, Aug 14 '23 at 12:29
mysql> alter table slides DROP key slides_module_id_order_unique; ERROR 1553 (HY000): Cannot drop index 'slides_module_id_order_unique': needed in a foreign key constraint — A. Khaled, Aug 14 '23 at 12:29
If you keep getting the same error message, then there are other foreign keys that use the same index. — Shadow, Aug 14 '23 at 16:35

How to drop multiple-column indexes on MySql?

0 Answers0