Cannot truncate a table referenced in a foreign key constraint

Question

Cannot truncate a table referenced in a foreign key constraint. What should i do first to enable truncate?

details:

(
    `guacamole`.`guacamole_connection_history`, 
    CONSTRAINT `guacamole_connection_history_ibfk_2` 
    FOREIGN KEY (`connection_id`) 
    REFERENCES `guacamole`.`guacamole_connection` (`connection_id`)
)

I want to clear guacamole_connection table for development testing.

Answer 1

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

Answer 2

You can do truncate by skipping foreign key checks.

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