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:
- DISABLE related FK
- TRUNCATE all related tables.
- 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;