4

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL?

I this answer I found a sentence as given below

"The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations"

This conflicts with MYSQL Reference Manual which says

"If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty."

I am in a confused state. Please elaborate on this issue.

Thank you

  • Which version of MySQL are you interested in? And which version of the documentation are you reading from and referring to? – ypercubeᵀᴹ Feb 10 '16 at 13:55
  • MYSQL 5.0 , just edited the question with links check out – geeksal Feb 10 '16 at 13:57
  • The link to the manual you posted is pretty cleaer: "the TRUNCATE TABLE statement does not invoke ON DELETE triggers" – a_horse_with_no_name Feb 10 '16 at 14:00
  • Yeah! indeed it does but the point of confusion is statement from 3rd para i.e "In the case that FOREIGN KEY constraints reference the table, InnoDB deletes rows one by one and processes the constraints on each one. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty." Isn't ON DELETE CASCADE is a trigger. – geeksal Feb 10 '16 at 14:05
  • The rows are deleted, but the delete trigger is not fired – a_horse_with_no_name Feb 10 '16 at 14:13
3

Your first link (Why is truncate DDL?) is about Oracle, not MySQL. While relevant, different DBMS implement same features differently. In Oracle and SQL Server TRUNCATE is DDL, not DML.

In MySQL, it matter which version you use:

Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For an InnoDB table:

  • If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.

  • When you use this fast truncation technique with the innodb_file_per_table option enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE. If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.

  • If there are any FOREIGN KEY constraints that reference the table, InnoDB processes TRUNCATE TABLE by deleting rows one by one, processing the constraints as it proceeds. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign  
    key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`  
    FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))  
    

But for versions 5.5 and later, the documentation says:

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.5:

...

| improve this answer | |
  • @a_horse_with_no_name The documentation states otherwise (for Postgres postgresql.org/docs/current/static/mvcc-caveats.html) – ypercubeᵀᴹ Feb 10 '16 at 14:16
  • @a_horse_with_no_name For SQL Server: TRUNCATE I made no remarks about being possible to roll it back in other DBMS (only for MySQL). – ypercubeᵀᴹ Feb 10 '16 at 14:18
  • I hope that you read my comment above, I am only concerned with, whether ON DELETE triggers are processed by TRUNCATE or not and if not (as appears in documentation) than what is "ON DELETE CASCADE" a ON DELETE trigger or something else. – geeksal Feb 10 '16 at 14:19
  • @geeksal do you use InnoDB or other engine? – ypercubeᵀᴹ Feb 10 '16 at 14:20
  • @ypercubeᵀᴹ InnoDB – geeksal Feb 10 '16 at 14:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.