To remove INDEX, remove the foreign key constraint

Aug 28, 2020 PHP MySQL Beginner Learning

To remove #INDEX, remove the foreign key constraint

If you want to delete the INDEX set in MySQL The error below. I felt it was a lot of work, so make a note of the procedure.

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

It seems that INDEX cannot be deleted because the foreign key is set.

That means. ． ． To delete the INDEX of the column for which the foreign key is set Foreign key deletion ⇒ INDEX deletion ⇒ Foreign key reset procedure is required! By the way, it is necessary to confirm the foreign key name to delete the foreign key.

As a procedure ** Foreign key name confirmation **

SHOW CREATE TABLE table-name;

Delete foreign key

ALTER TABLE table name DROP FOREIGN KEY foreign key name;

Check index name

SHOW INDEX FROM table-name;

Delete index

ALTER TABLE table name DROP INDEX index name;

Re-paste external key

ALTER TABLE table name ADD FOREIGN KEY (column name) REFERENCES primary key table name (primary key column name) ON DELETE CASCADE ON UPDATE CASCADE;

bonus To re-attach the index, set the index further. Index settings

ALTER TABLE table name ADD INDEX any index name (column name you want to paste, column name you want to paste);

that’s all.