Remove rows with DELETE
DELETE removes the rows that its WHERE condition keeps:
DELETE FROM carts WHERE id = 2;The Result tab shows 1 row deleted. Always write the WHERE: without it, DELETE removes every row of the table. Chapter 6 shows more about DELETE. This lesson needs only this form.
A referenced row cannot go
products references categories. Remove category 1, which has three products:
ERROR: update or delete on table "categories" violates foreign key constraint "products_category_id_fkey" on table "products"
DETAIL: Key (id)=(1) is still referenced from table "products".This is the default rule, NO ACTION. RESTRICT gives the same result. The difference is about the time of the test, and it matters only in transactions (Chapter 14).
Choose what happens to the children
Write the rule after REFERENCES:
category_id integer REFERENCES categories (id) ON DELETE SET NULL| Rule | When the parent row goes, its child rows… | Use it when |
|---|---|---|
NO ACTION (default), RESTRICT | stop the DELETE with an error | the children must not be lost: a customer with orders |
CASCADE | are removed too | a child has no meaning without its parent: the items of an order |
SET NULL | stay, with NULL in the foreign key | a child can exist alone: a product without a category |
products after DELETE FROM categories WHERE id = 1, with CASCADE
| id | name | category_id |
|---|---|---|
| 1 | Kraken Plush | 1 |
| 2 | Pod Golem Plush | 1 |
| 4 | Container Crab Tee | 2 |
products after DELETE FROM categories WHERE id = 1, with SET NULL
| id | name | category_id |
|---|---|---|
| 1 | Kraken Plush | NULL |
| 2 | Pod Golem Plush | NULL |
| 4 | Container Crab Tee | 2 |
SET NULL needs a column that accepts NULL. With NOT NULL on the column, the DELETE fails.
CASCADE can remove many rows with one short statement. A CASCADE from orders to items is safe. A CASCADE from customers to orders removes the sales history. Choose it with care.