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.