Mysql resets foreign key without copying table

Question

I have an InnoDB claims table that contains about 240 million rows. The table has a foreign key constraint: CONSTRAINT FK78744BD7307102A9 FOREIGN KEY (ID) REFERENCES claim_details (ID) . I want to delete the claim_details table as quickly as possible.

Based on some experiments, it seems that if I use SET foreign_key_checks = 0; drop claim_details and then re-activate the foreign keys, mysql will continue to enforce the constraint even if the table no longer exists. So, I believe that I should remove the restriction from the table.

I tried using ALTER TABLE claims DROP FOREIGN KEY FK78744BD7307102A9 to remove the restriction, and the request was in the "copy to tmp table" state for more than 24 hours (on a machine without any other load). I do not understand why a reset of the table requires a copy of the table. Is there any way to prevent this?

mysql version 5.1.48.

Answer 1

I think there is no good way to reset this foreign key

http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html

"MySQL 5.5 does not support the efficient creation or removal of FOREIGN KEY constraints. Therefore, if you use ALTER TABLE to add or remove REFERENCES constraints, the child table is copied rather than using Fast Index Creation." This probably also applies to older versions of mysql.

I think that the best method would be to dump data from claims using mysqldump , recreate the table without a foreign key, referring to claim_details , disable key verification with SET foreign_key_checks = 0; if you have other foreign keys and import back data for claims . Remember to make separate dumps for the data and structure, so you do not need to edit this huge file to remove the foreign key from the table creation syntax.

Answer 2

Starting with MySQL 5.6, MySQL supports the removal of foreign keys in place / without copying. Oracle calls it online DDL.

This table lists all DDL statements and their runtime behavior.

In my experience, deleting foreign keys and corresponding restrictions in a 600 GB table is almost instantaneous. With 5.5, it would probably take several days.

The only drawback that I know of is that 5.6 does not allow you to restore table space. That is, if you use innodb_file_per_table , this file will not shrink when you drop indexes. Only unused data in the file will grow. You can easily check with the SHOW TABLE STATUS and the Data_free column.