Cannot truncate a table referenced in a foreign key constraint

TRUNCATE it's not equivalent to DELETE: TRUNCATE it's DDL operations while DELETE is a DML operation. In other words TRUNCATE alter the table structure (i.e. freeing storage and modifying other properties depending on RDBMS you are working on) while DELETE just modify the data on in performing every validation that your model has specified (i.e. foreing key constraints, check constraints, etc.)

Why would you want to truncate the table? Well, it's faster as it doesn't has to run any validation (that's why your FK are affecting the truncate operation), and allows you to free all the space the table (and it's index) is currently allocating.

So, if you want to truncate your table you should:

  1. DISABLE related FK
  2. TRUNCATE all related tables.
  3. ENABLE the previously disabled FKs

Internally the operation use to be (again depending on the RDBMS) equivalent to DROP and CREATE the FKs. The difference usually is related to the permissions needed as conceptually it's not the same to create/delete a FK than enable/disable it


Why not add a constraint adding ON DELETE CASCADE and ON UPDATE CASCADE? Then all you need to do is TRUNCATE guacamole_connection CASCADE

Example:

ALTER TABLE 
    guacamole_connection_history 
ADD CONSTRAINT 
    guacamole_connection_history_cascade_delete 
    FOREIGN KEY (connection_id) 
    REFERENCES guacamole_connection (connection_id) 
    ON UPDATE CASCADE ON DELETE CASCADE;

Then just run TRUNCATE guacamole_connection CASCADE


You can do truncate by skipping foreign key checks.

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table1; 
SET FOREIGN_KEY_CHECKS = 1;

Tags:

Mysql

Sql