PostgreSQL: FOREIGN KEY/ON DELETE CASCADE

Question

I have two tables like here:

DROP   TABLE  IF EXISTS schemas.book;
DROP   TABLE  IF EXISTS schemas.category;
DROP   SCHEMA IF EXISTS schemas;
CREATE SCHEMA schemas;

CREATE TABLE schemas.category (
  id          BIGSERIAL PRIMARY KEY,
  name        VARCHAR   NOT NULL,
  UNIQUE(name)
);

CREATE TABLE schemas.book (
  id          BIGSERIAL PRIMARY KEY,
  published   DATE      NOT NULL,
  category_id BIGINT    NOT NULL REFERENCES schemas.category
                            ON DELETE CASCADE 
                            ON UPDATE CASCADE,
  author      VARCHAR   NOT NULL,
  name        VARCHAR   NOT NULL,
  UNIQUE(published, author, name),
  FOREIGN KEY(category_id) REFERENCES schemas.category (id)
);

So the logic is simple, after user removes all book under category x, x gets removed from cats, i tried method above but doesn't work, after i clean table book, table category still populated, what's wrong?

Yes, see [crosspost](http://stackoverflow.com/questions/14161212/how-to-create-one-table-which-shares-common-id-with-another-and-which-row-gets-r/14161296#comment19641520_14161296) — juk, Jan 06 '13 at 14:34
Does this answer your question? [SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?](https://stackoverflow.com/questions/13444859/sql-on-delete-cascade-which-way-does-the-deletion-occur) — Martin Thoma, Dec 13 '22 at 08:32

score 126 · Accepted Answer · edited Dec 24 '22 at 02:22

126

A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.

You are saying in a opposite way, this is not that when you delete from child table then records will be deleted from parent table.

UPDATE 1:

ON DELETE CASCADE option is to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behaviour of the database server prevents you from deleting data in a table if other tables reference it.

If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.

So it's all about what will happen when you delete rows from Parent table not from child table.

So in your case when user removes entries from categories table then rows will be deleted from books table.

edited Dec 24 '22 at 02:22

starball

20,030
7
43
238

answered Jan 03 '13 at 14:58

Mariappan Subramanian

9,527
8
32
33

51

There is no tree structure in SQL. You should really try to rewrite this answer in terms of references, not "parents" nor "children". – Martin Nov 19 '15 at 14:59
6

There *are* parent tables in the OO-inheritance aspects of postgres, but this is even more reason the answer should avoid parent/child terminology and describe *references*. – Joe Atzberger Apr 27 '17 at 01:10
2

I'm pretty convinced @Mari understands what CASCADE specifies, but this is an incorrect answer in terms of terminology. As hinted above, in inherited tables children are always deleted when the (same) row is deleted from the parent table. As a side note I guess that the default behavior of the server would be specific to the actual server. In Postgres the default behavior is RESTRICT. – Martin May 18 '17 at 06:51
7

It may be incorrect terminology (none of you have provided a better alternative) but it helps understand what's meant – May 16 '20 at 01:22
4

The terminology is ambiguous. Both the parent and child class of a hierarchical structure could be holding the FK constraint, so this terminology does not help understanding which deletion causes the other deletion. It would be better if you call them "the table where the constraint is defined" (or the "owner" of the relation) and "the referenced table". – Daniele Repici Oct 19 '20 at 14:22
1

The provided alternative can indeed be found in another answer. @user12207064 – Martin Jun 08 '21 at 09:31
Thank youu for the answer after 9 years! – twiny Sep 25 '22 at 21:42

Martin · Answer 2 · 2021-06-16T07:50:26.830

Excerpt from PostgreSQL documentation:

Restricting and cascading deletes are the two most common options. [...] CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

This means that if you delete a row in schemas.category, referenced by category_id in schemas.books, any such referencing row will also be deleted by ON DELETE CASCADE.

Example:

CREATE SCHEMA shire;

CREATE TABLE shire.clans (
    id serial PRIMARY KEY,
    clan varchar
);

CREATE TABLE shire.hobbits (
    id serial PRIMARY KEY,
    hobbit varchar,
    clan_id integer REFERENCES shire.clans (id) ON DELETE CASCADE
);

DELETE FROM clans will CASCADE to hobbits by REFERENCES.

sauron@mordor> psql
sauron=# SELECT * FROM shire.clans;
 id |    clan    
----+------------
  1 | Baggins
  2 | Gamgi
(2 rows)

sauron=# SELECT * FROM shire.hobbits;
 id |  hobbit  | clan_id 
----+----------+---------
  1 | Bilbo    |       1
  2 | Frodo    |       1
  3 | Samwise  |       2
(3 rows)

sauron=# DELETE FROM shire.clans WHERE id = 1 RETURNING *;
 id |  clan   
----+---------
  1 | Baggins
(1 row)

DELETE 1
sauron=# SELECT * FROM shire.hobbits;
 id |  hobbit  | clan_id 
----+----------+---------
  3 | Samwise  |       2
(1 row)

If you really need the opposite (checked by the database), you will have to write a trigger!

this has been the explanation that finally made me understand what this REFERENCES / CASCADE syntax actually means, much appreciated! — Clint Eastwood, Feb 25 '19 at 16:48

score -8 · Answer 3 · answered Feb 07 '18 at 18:42

-8

In my humble experience with postgres 9.6, cascade delete doesn't work in practice for tables that grow above a trivial size.

Even worse, while the delete cascade is going on, the tables involved are locked so those tables (and potentially your whole database) is unusable.
Still worse, it's hard to get postgres to tell you what it's doing during the delete cascade. If it's taking a long time, which table or tables is making it slow? Perhaps it's somewhere in the pg_stats information? It's hard to tell.

answered Feb 07 '18 at 18:42

Gordon Vidaver

9
1

6

That is the definition of working, in a database with proper transactions. Secondly, this is not an answer to the question. – Martin Feb 09 '18 at 16:26
3

In case someone stumbles upon this: probably you have overlooked this explanation in the documentation, at the end of 5.3.5: "Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too." https://www.postgresql.org/docs/9.5/ddl-constraints.html – Michael Kraxner Jul 08 '21 at 13:18

score -23 · Answer 4 · answered Jul 15 '17 at 10:50

-23

PostgreSQL Forging Key DELETE, UPDATE CASCADE

CREATE TABLE apps_user(
  user_id SERIAL PRIMARY KEY,
  username character varying(30),
  userpass character varying(50),
  created_on DATE
);

CREATE TABLE apps_profile(
    pro_id SERIAL PRIMARY KEY,
    user_id INT4 REFERENCES apps_user(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
    firstname VARCHAR(30),
    lastname VARCHAR(50),
    email VARCHAR UNIQUE,
    dob DATE
);

answered Jul 15 '17 at 10:50

Ram Pukar

1,583
15
17

5

This cut-and-paste is a poor example with no relation to the question, and the most important keyword is swapped for a random that kind of sounds the same. – Martin Aug 15 '17 at 15:03
3

to be fair, 'FORGING' sort-of gives it away. – John Frazer Feb 12 '18 at 15:13

PostgreSQL: FOREIGN KEY/ON DELETE CASCADE

4 Answers4

Linked