ERROR 1553 (HY000): Cannot drop index ‘pid’: needed in a foreign key constraint

Cannot drop index ‘pid’: needed in a foreign key constraint

Error and Structure of table:

MariaDB [test]> desc c1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| cid   | int(11) | NO   | PRI | NULL    |       |
| pid   | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.044 sec)

MariaDB [test]> show index from c1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| c1    |          0 | PRIMARY  |            1 | cid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| c1    |          1 | pid      |            1 | pid         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.024 sec)

MariaDB [test]> drop index pid on c1;
ERROR 1553 (HY000): Cannot drop index 'pid': needed in a foreign key constraint

Solution: Rebuild the index by disabling foreign key at the database level.

MariaDB [test]> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.008 sec)

MariaDB [test]> DROP INDEX `pid` ON c1;
Query OK, 0 rows affected (0.054 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE INDEX `pid` ON c1(`pid`);
Query OK, 0 rows affected (0.088 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.000 sec)

Note: Please lock the table, so that during rebuild operation no one inserts or uses the table.

-- Lock the table 
LOCK TABLES table_name WRITE;
-- unlock the table:
UNLOCK TABLES;
-- Check the use of table or lock
show open tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.