Can't truncate MySQL table, while being able to delete all the records

Question

I'm a very beginner to relations, so this may sound dumb. But, what is the difference (in MySQL) between truncating a table and removing all the records (this answer says only about performance)?

I was playing (in phpMyAdmin) with one of my test tables, to check, how can I reset auto_increment value of table and run into situation, where I was able to delete all the records:

DELETE from managers;

But when I tried to truncate this table (TRUNCATE managers), I've got warning: Cannot truncate a table referenced in a foreign key constraint (probes, CONSTRAINT probes_ibfk_4 FOREIGN KEY (manager_id) REFERENCES managers (id));.

I had to "reset" the auto_increment value with ALTER TABLE managers AUTO_INCREMENT = 1;.

Isn't that something odd? Up until now, I thought, that TRUNCATE = DELETE from managers (in terms of checking and effects, because performance can be different, but this is not the key here).

How can constraint warning pop on TRUNCATE but not on "delete all"?

http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table maybe? Your table references itself via foreign key... — Jasper, Apr 03 '14 at 13:56
Where do you see, that my table references itself? There is no such reference in either database schema or constraint keys. I must be missing something. — trejder, Apr 03 '14 at 14:16
`FOREIGN KEY (manager_id)` references `managers` table (`id` column), but this (`manager_id` column) is a column from _different_ table. So, it does not reference itself. MySQL (phpMyAdmin) error message may be a little bit misleading. — trejder, Apr 03 '14 at 18:27

Volkan Ulukut · Accepted Answer · 2020-09-09T15:51:59.883

truncate resets auto_increment after deleting all rows. so it's not the same as deleting all rows with DELETE command.

from mysql reference:

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:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

Truncate operations cause an implicit commit, and so cannot be rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

Truncation operations cannot be performed if the session holds an active table lock.

TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

I have managed to reset `auto_increment` with `ALTER TABLE managers AUTO_INCREMENT = 1;`, so your direct answer may be misleading (the fact, that `TRUNCATE` resets `auto_increment` has nothing to do, as I did the same). However, text cited by you does give me an idea, what is going on, so I'm accepting your answer. Thank you. — trejder, Apr 03 '14 at 18:29
The link is dead-like. It points to a page that is redirected to MySQL 8.0 and has no references to MySQL 5.0.3 (which behaves in a totally different way from the way provided answer states). — ilias iliadis, Jul 29 '19 at 15:10

Can't truncate MySQL table, while being able to delete all the records

1 Answers1