DELETE, TRUNCATE, and REPLACE

DELETE removes the rows its WHERE matches, or all of them. TRUNCATE TABLE empties a table by re-creating it. REPLACE inserts a row after deleting any row with the same primary or unique key.

Deleting rows, and the foreign keys that refuseSQL

DELETE FROM reviews WHERE id = 7;
DELETE FROM customers WHERE id = 1;           -- Ana has orders
TRUNCATE TABLE orders_archive;                -- holds one row
TRUNCATE TABLE products;
REPLACE INTO authors (id, name, country) VALUES (1, 'Rasmus Lerdorf', 'GL');
SELECT id, name, born FROM authors WHERE id = 1;

Output

Query OK, 1 row affected (0.007 sec)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES
    `customers` (`id`))
Query OK, 0 rows affected (0.076 sec)
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
  (`shop`.`order_items`, CONSTRAINT `order_items_ibfk_2`)
Query OK, 2 rows affected (0.006 sec)
+----+----------------+------+
| id | name           | born |
+----+----------------+------+
|  1 | Rasmus Lerdorf | NULL |
+----+----------------+------+
1 row in set (0.000 sec)

The foreign keys protect the order history (Foreign Keys covers ON DELETE CASCADE). TRUNCATE reports 0 rows whatever the table held.

DELETE versus TRUNCATE TABLE on InnoDB
Property	DELETE	TRUNCATE TABLE
WHERE clause	Yes	No, whole table
Rollback	Inside a transaction	No, implicit commit
AUTO_INCREMENT	Keeps counting	Resets
Delete triggers	Fire	Do not fire

REPLACE reported 2 rows, one deleted and one inserted, and Rasmus lost his birth year because born was not in the statement. It is a delete plus an insert, so it also fires delete triggers and trips foreign keys. Change existing rows with UPDATE or an upsert.

MENU

DELETE, TRUNCATE, and REPLACE