CODEWITHSUNDEEP
mysqlforeign-keys
treyBake
treyBake

Reputation: 6560

Why can I delete but not truncate?

so I had a bunch of test rows in a table:

+----+--------+---------+-----------+----------+---------------------+--------------+
| id | name   | guru_id | parent_id | approved | created_ts          | last_edit_ts |
+----+--------+---------+-----------+----------+---------------------+--------------+
|  1 | PHP    |       1 |      NULL |        0 | 2018-07-12 14:27:05 | NULL         |
|  2 | HTML   |       1 |      NULL |        0 | 2018-07-12 14:33:21 | NULL         |
|  3 | CSS    |       1 |      NULL |        0 | 2018-07-12 14:34:54 | NULL         |
|  4 | test   |       1 |      NULL |        0 | 2018-07-12 14:37:11 | NULL         |
|  5 | test   |       1 |      NULL |        0 | 2018-07-12 14:38:39 | NULL         |
|  6 | test   |       1 |      NULL |        0 | 2018-07-12 14:40:39 | NULL         |
|  7 | test   |       1 |      NULL |        0 | 2018-07-12 14:43:29 | NULL         |
|  8 | test   |       1 |      NULL |        0 | 2018-07-12 14:43:59 | NULL         |
|  9 | test   |       1 |      NULL |        0 | 2018-07-12 14:45:12 | NULL         |
| 10 | test   |       1 |      NULL |        0 | 2018-07-12 14:45:19 | NULL         |
| 11 | test   |       1 |      NULL |        0 | 2018-07-12 14:45:39 | NULL         |
| 12 | CSS    |       1 |      NULL |        0 | 2018-07-12 14:46:30 | NULL         |
| 13 | trey   |       1 |      NULL |        0 | 2018-07-12 14:48:47 | NULL         |
| 14 | trey   |       1 |      NULL |        0 | 2018-07-12 14:49:36 | NULL         |
| 15 | trey   |       1 |      NULL |        0 | 2018-07-12 14:50:52 | NULL         |
| 16 | ajax   |       1 |      NULL |        0 | 2018-07-12 14:51:23 | NULL         |
| 17 | jquery |       1 |      NULL |        0 | 2018-07-12 14:51:44 | NULL         |
+----+--------+---------+-----------+----------+---------------------+--------------+

I eventually got my code working how I wanted, so I tried to TRUNCATE the table to start with fresh data using:

mysql> truncate category

which returns this error:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (db.page, CONSTRAINT FK_140AB62012469DE2 FOREIGN KEY (category_id) REFERENCES db.category (id))

I thought as a joke to get round it try this:

> delete from category where id != 0;

and to my surprise, the command worked. Now I have an empty table as desired, but not sure why delete from surpasses the foreign key check but not truncate? Is there any logic/reason behind this?

Upvotes: 1

Views: 632

Answers (1)

Dragonthoughts
Dragonthoughts

Reputation: 2224

A delete command will take account of the cascade deletion (if it is on) or fail if it isn't.

Truncate would only act on the table and not linked records. Truncate essentially tries to drop all the data pages and indices for the table without regard to other tables - foreign key constraints prevent this.

Truncate is effectively drop table followed by create table of same schema.

Upvotes: 3

Related Questions