What is a faster way to truncate a group of tables in MySQL?

Question

I’m using MySql 5.5.37. I’m trying to truncate data from a group of tables. I read here -- Quickest way to delete enormous MySQL table, that re-creating/renaming the tables was supposedly faster, but I’m getting errors …

SET FOREIGN_KEY_CHECKS=0;
…
CREATE TABLE IF NOT EXISTS new_organization LIKE organization; 
RENAME TABLE organization TO old_t, new_organization TO organization; 
DROP TABLE old_t;

CREATE TABLE IF NOT EXISTS new_organization_address LIKE organization_address; 
RENAME TABLE organization_address TO old_t, new_organization_address TO organization_address; 

ERROR 1025 (HY000): Error on rename of './sbjunit_ebook/organization_address' to './sbjunit_ebook/old_t' (errno: 150)

Looking further into the problem, it says …

Error in foreign key constraint of table sbjunit_ebook/old_t:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "FK1_ORGANIZTION_ADDRESS" FOREIGN KEY ("ORGANIZATION_ID") REFERENCES "old_t" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION
The index in the foreign key in table is "UK_ORGANIZATION_ADDRESS"

Why is renaming the tables not updating the foreign key references and what is a faster way to truncate data from a group of tables?

Answer 1

Renaming a table (using the RENAME TABLE statement) doesn't affect the foreign keys referencing the table. The foreign keys that reference the table get "updated" only in the sense that they are unaffected, they aren't "lost". Only the name of the table is affected; the foreign key references referring to the table aren't lost.

When you disable foreign_key_checks, you are essentially telling the database that you are taking on responsibility for maintaining the integrity of the data. It doesn't instruct MySQL to "lose" foreign key constraint definitions when tables are renamed.

In this case, when the table named old_t is dropped, MySQL assumes that a table named old_t will be recreated, and that it will have a suitable index (column names, column datatypes) that the existing foreign keys will reference. It's also assuming that appropriate rows will be inserted so that all of the foreign key constraints will be satisfied.

(Note that the DROP TABLE statement would not have succeeded if FOREIGN_KEY_CHECKS was enabled, the statement would have returned an error.)

---

The fastest way to empty that table would actually be to disable the foreign key constraints that reference the table, and then perform a TRUNCATE statement on the table. MySQL will actually perform the TRUNCATE operation (under the covers) by creating a new empty table, and dropping the old table.

You could follow the same pattern for other tables.

At that point, you would re-enable the the foreign key constraints.

(MySQL doesn't actually support DISABLE/ENABLE of foreign key constraints; we have to DROP and ADD the foreign key constraints.

ALTER TABLE bar DROP 
 FOREIGN KEY (foo_id) ;

ALTER TABLE bar ADD 
  CONSTRAINT FK_bar_foo 
  FOREIGN KEY (foo_id) REFERENCES foo (id) ;

(Note that creating a foreign key constraint on "large" tables can take a bit of time, but if table bar is empty, it will be really fast.)

---

UPDATE

The information_schema tables can be queried to help build the DROP and ADD of the foreign keys.

It's important to "save off" the existing foreign key constraints before dropping them.

This SQL statement works for generating statements to DROP and ADD of foreign key constraints on some of my tables. (All of my foreign key constraints have user supplied names, no system generated names.) Your mileage may vary:

For foreign keys referencing mydatabase.mytable

SELECT CONCAT('ALTER TABLE `',r.constraint_schema,'`.`',r.table_name
             ,'` ADD'
             ,' CONSTRAINT `',r.constraint_name,'`'
             ,' FOREIGN KEY ('
             ,GROUP_CONCAT(CONCAT('`',k.column_name,'`') 
                           ORDER BY k.ordinal_position)
             ,') REFERENCES ',r.referenced_table_name
             ,' ('
             ,GROUP_CONCAT(CONCAT('`',k.referenced_column_name,'`')
                           ORDER BY k.ordinal_position)
             ,')'
             ,' ON UPDATE ',r.update_rule
             ,' ON DELETE ',r.delete_rule
             ,';'
       ) AS add_fk
     , CONCAT('ALTER TABLE `',r.constraint_schema,'`.`',r.table_name
              ,'` DROP FOREIGN KEY '
              ,'`',r.constraint_name,'`'
              ,';'
       ) AS drop_fk
  FROM information_schema.referential_constraints r
  JOIN information_schema.key_column_usage k
       ON k.constraint_schema = r.constraint_schema 
       AND k.table_name  = r.table_name
       AND k.constraint_name = r.constraint_name
 WHERE r.constraint_schema = 'mydatabase'
   AND r.referenced_table_name = 'mytable'
 GROUP BY r.constraint_schema, r.table_name, r.constraint_name ;