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 the user deletes the entire book under category x, x is removed from the cats, I tried the method above, but it doesn’t work, after I cleared the book table, the table category is still full, what’s wrong?

Answer 1

A foreign key with cascading deletion means that if an entry in the parent table is deleted, the corresponding entries in the child table will be automatically deleted. This is called cascading deletion.

You say the opposite, this does not mean that if you delete from the child table, the records will be deleted from the parent table.

 UPDATE 1: 

ON DELETE CASCADE - indicate whether you want to delete rows in the child table when the corresponding columns are deleted in the parent table. If you do not specify cascading deletions, the default behavior of the database server by default does not allow you to delete data in a table if it refers to other tables.

If you specify this parameter, later, when you delete a row in the parent table, the database server will also delete any rows associated with this row (foreign keys) in the child table. The main advantage of the cascade-delete function is that it allows you to reduce the number of SQL statements needed to perform the delete actions.

So, all that happens when you delete rows from the parent table, and not from the child table.

So, in your case, when the user deletes entries from the CATs table, the rows will be deleted from the book table. :)

Hope this helps you :)

Answer 2

Exerpt from PostgreSQL documentation :

Restrictive and cascading deletions are the two most common options. [...] CASCADE indicates that when a reference line is deleted, lines (lines) that refer to it should also be automatically deleted.

This means that if you delete the category referenced by the books, the reference book will also be deleted using ON DELETE CASCADE .

An 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 for hobbits at 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!

Answer 3

In my humble experience with postgres 9.6, cascading deletion does not work in practice for tables that grow above trivial size.

• Even worse, while the deletion cascade continues, the tables involved are locked, so these tables (and possibly your entire database) are unusable.
• However, it is difficult to get postgres to tell you that this is done during the removal of the cascade. If it takes a long time, which table or tables slows down? Perhaps this is somewhere in the pg_stats info? Hard to say.

Answer 4

Stamping key PostgreSQL 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 );