如何为postgres编写DELETE CASCADE？ [英] How does one write a DELETE CASCADE for postgres?

问题描述

我正在为postgres手动构建DELETE CASCADE语句。

I'm manually constructing a DELETE CASCADE statement for postgres.

我有一个交易表和一个切片表，如下所示：

I have a 'transaction' and a 'slice' table, related as shown below:

    Table "public.slice"
  Column  | Type | Modifiers 
----------+------+-----------
 id       | text | not null
 name     | text | 
Referenced by:
    TABLE "transaction" CONSTRAINT "transaction_slice_id_fkey" FOREIGN KEY (slice_id) REFERENCES slice(id)
 Table "public.transaction"
  Column  | Type | Modifiers 
----------+------+-----------
 id       | text | not null
 slice_id | text | 
Referenced by:
    TABLE "classification_item" CONSTRAINT "classification_item_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transaction(id)
Table "public.classification_item"
     Column     | Type | Modifiers 
----------------+------+-----------
 id             | text | not null
 transaction_id | text | 
Foreign-key constraints:
    "classification_item_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transaction(id)

说我想删除名称为 my_slice的切片所引用的所有事务和分类项。我需要写什么？

Say I want to delete all transactions and classification_items referenced by the slice whose name is 'my_slice'. What do I need to write?

=# delete from classification_item where transaction_id= #...? 
=# delete from transaction where slice_id= #...? 
=# delete from slice where name='my_slice';

推荐答案

如果您不能做别人建议的事情：

In case you can't do what others have suggested:

begin;
delete from classification_item where transaction_id in (select id from "transaction" where slice_id = (select id from slice where name = 'my_slice'));
delete from "transaction" where slice_id in (select id from slice where name='my_slice');
delete from slice where name='my_slice';
commit;

这篇关于如何为postgres编写DELETE CASCADE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！