CODEWITHSUNDEEP
mysqlsql
Arek Kostrzeba
Arek Kostrzeba

Reputation: 579

Cannot truncate a table referenced in a foreign key constraint

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.

DB structure

Upvotes: 12

Views: 26193

Answers (5)

GeM
GeM

Reputation: 97

You just add CASCADE end of query:

TRUNCATE TABLE table_name CASCADE;

Upvotes: 0

CAllen
CAllen

Reputation: 836

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

Upvotes: 3

Abhishek Goel
Abhishek Goel

Reputation: 19751

You can do truncate by skipping foreign key checks.

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

Upvotes: 24

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Be sure to remove entries from any dependent tables first. For example:

 TRUNCATE TABLE guacamole_connection_history;
 TRUNCATE TABLE guacamole_connection;

Upvotes: -2

vamaq
vamaq

Reputation: 331

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

Upvotes: 7

Related Questions