删除行以及引用已删除行的其他行

我正在使用 PostgreSQL 并且我有一张表family像这样：

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

现在，当我删除这个人时，我也希望孩子们也被删除。例如删除WHERE name='peter'删除'peter' and 'pan'从列表中。 当我删除时'eva', she, 'peter' and 'pan'被删除。

现在我考虑删除初始行并从删除的行中获取 id。然后我会再次删除，直到现在 id 被返回。请注意，一个人只有一个父母，但可以有几个孩子。

SQL中有没有一种巧妙的方法来解决这个问题？如果没有，我如何取回所有已删除行的 id？

最好的解决方案是创建一个正确的外键，其定义为on delete cascade。这需要存储一个NULLParent_id 列中的值而不是神奇的“零”：

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

那么你所需要的就是：

delete from family
where name = 'peter';

如果你想转换现有的表和数据，你可以这样做：

--- 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;