PostgreSQLで親テーブルと子テーブルのレコードを同時に削除したい

Question

前提・実現したいこと

PostgreSQLで以下のような親子関係を持っているテーブルにおいて、Aのテーブルのレコードを削除したいです。
外部キー制約がある場合、子から削除しないといけないのは理解しましたが、DELETE文でまとめて親と子を削除する方法が分かりません。
教えていただけるとありがたいです。

テーブルの親子関係

A　Bの親
↓
B　Aの子でありCの親
↓
C　Bの子

Answer 1

DELETE文でまとめて親と子を削除する方法

子は親の道連れになるように定義する。

$ psql
> create table parent(id int primary key, value text);
> create table child(parentId int REFERENCES parent(id) ON DELETE CASCADE, value text);

> insert into parent values (1, 'aa'), (2, 'bb');
> insert into child values (1, 'xx'), (1, 'yy'), (2, 'zz');

> select * from parent;
 id | value
----+-------
  1 | aa
  2 | bb
(2 rows)

> select * from child;
 parentid | value
----------+-------
        1 | xx
        1 | yy
        2 | zz
(3 rows)

> delete from parent where id = 2;

> select * from parent;
 id | value
----+-------
  1 | aa
(1 row)

> select * from child;
 parentid | value
----------+-------
        1 | xx
        1 | yy
(2 rows)

Comments

[komekoma]

回答ありがとうございます。
まとめて削除することはできたのですが、ON DELETE CASCADE で設定する以外ではまとめて消す手段はないでしょうか？INNER JOINで結合して削除する方法を試してみましたができなかったので、、、

Answer 2

参照整合性による連鎖削除の方法が取れない場合は、WITH式によって異なる操作を同時に行うことが出来ます。
7.8.2. WITH内のデータ変更文

with c_del as (
 delete from c where xxxx
 returning *
) , b_del as (
 delete from b where キー in (select 親のキー from c_del)
 returning *
)
delete from a where キー in (select 親のキー from b_del)

Comments

[shiketa]

なるほど。~~勉強になります。参照整合性を外したわたしの例だとこうなりますね。with child_del as ( delete from child where parentId = 2 returning * ) delete from parent where id in (select parentId from child_del) ;~~

[sazi]

質問では孫までいるように書かれていますが？

[komekoma]

回答ありがとうございます！
回答を参考にして無事sqlが書けました！