DELETE removes rows from a table.
sql
DELETE FROM categories
WHERE id = 5;DELETE FROM categoriesremove rows from this tableWHERE id = 5only these rows
categories
| id | name |
|---|---|
| 1 | Plush |
| 2 | Apparel |
| 3 | Stickers |
| 4 | Mugs |
| 5 | Posters |
- DELETE removes whole rows. To clear one value, use UPDATE and set it to NULL.
- Without WHERE, DELETE removes every row. The same rule as UPDATE applies: run the WHERE as a SELECT first.
- The output shows the count:
1 row deleted.
Foreign keys can block a DELETE
orders.customer_id refers to customers.id (Chapter 5). Customer 1 has orders, so this fails:
text
DELETE FROM customers WHERE id = 1;
ERROR: update or delete on table "customers" violates foreign key
constraint "orders_customer_id_fkey" on table "orders"order_items1. the rows that refer to it
orders2.
customers3. the row they referred to
The error names the table that still points to the row. Delete those rows first, or define the key with ON DELETE CASCADE (Chapter 5).
DELETE, TRUNCATE or DROP
TRUNCATE removes every row of a table, faster than DELETE on a large table. It takes no WHERE.
text
TRUNCATE cart_items;| Statement | Removes | WHERE | The table after |
|---|---|---|---|
DELETE FROM t WHERE … | the rows that match | yes | stays |
TRUNCATE t | every row | no | stays, empty |
DROP TABLE t | the rows and the table | no | gone |
TRUNCATE also fails when a foreign key in another table refers to the table.