Delete rows and other rows with a reference to the deleted row

Question

I'm working with PostgreSQL and I have a table family like this:

+----+-------+-----------+
| id | name  | parent_id |
+----+-------+-----------+
|  1 | adam  |         0 |
|  2 | eva   |         0 |
|  3 | peter |         2 |
|  4 | pan   |         3 |
+----+-------+-----------+

Now when I delete the person, I also want that the children to get deleted. So for example deleting WHERE name='peter' deletes 'peter' and 'pan' from the list. When I delete 'eva', she, 'peter' and 'pan' get deleted.

Now I thought about deleting the initial row and get the id from the deleted row. Then I'd proceed to delete again, so long until now id gets returned. Mind you, person has only on parent, but can have several children.

Is there a neat way in SQL to solve this problem? If not, how do I get all deleted rows' ids back?

Answer 1

The best solution is to create a proper foreign key that is defined with on delete cascade. Which requires to store a NULL value rather than a magic "zero" in the parent_id column:

create table family 
(
  id int primary key, 
  name varchar(5), 
  parent_id int, 
  foreign key (parent_id) references family on delete cascade
);

Then all you need is:

delete from family
where name = 'peter';

Online example

---

If you want to convert your existing table and data, you can do it like this:

--- change all zeroes to NULL
update family 
  set parent_id = null
where parent_id = 0;

-- add a primary key in order to be able to create a foreign key
alter table family add primary key (id);

-- add the foreign key 
alter table family 
  add foreign key (parent_id) references family (id)
  on delete cascade;

Answer 2

You can use a recursive CTE to generate the list of child ids associated with a given person, and then delete all the ids from that table:

WITH RECURSIVE CTE AS (
  SELECT id
  FROM family 
  WHERE name = 'eva'
  UNION ALL
  SELECT f.id
  FROM family f
  JOIN CTE ON f.parent_id = CTE.id
)
DELETE
FROM family
WHERE id IN (SELECT id FROM CTE)

Demo on SQLFiddle