SQL 如何为 postgres 编写 DELETE CASCADE？

How does one write a DELETE CASCADE for postgres?

提问by AP257

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

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

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)

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?

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

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

采纳答案by Milen A. Radev

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;

回答by jira

Postgres foreign keys support the CASCADE deletes:

Postgres 外键支持 CASCADE 删除：

slice_id integer REFERENCES slice(id) ON DELETE CASCADE

etc

等等

回答by Abe Miessler

It's soemthing that is defined in the table rather than the DELETE Query. Example (look at order_id):

它是在表中而不是 DELETE 查询中定义的东西。示例（查看 order_id）：

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

回答by tanius

You should use CASCADE deletes, and it should be possible to do so even if you inherited a database schema. You would just modify the constraints to add the CASCADE deletes to the schemas:

您应该使用 CASCADE 删除，即使您继承了数据库架构，也应该可以这样做。您只需修改约束以将 CASCADE 删除添加到架构中：

1. Drop and re-create the constraints to add CASCADE deletes:

   ALTER TABLE ONLY "transaction" 
       DROP CONSTRAINT transaction_slice_id_fkey;
   ALTER TABLE ONLY "transaction" 
       ADD CONSTRAINT transaction_slice_id_fkey 
       FOREIGN KEY (slice_id) REFERENCES slice(id) ON DELETE CASCADE;
   
   ALTER TABLE ONLY "classification_item" 
       DROP CONSTRAINT classification_item_transaction_id_fkey;
   ALTER TABLE ONLY "classification_item" 
       ADD CONSTRAINT classification_item_transaction_id_fkey 
       FOREIGN KEY (transaction_id) REFERENCES transaction(id) ON DELETE CASCADE;
   

2. Now the following query will delete not just the my_slicerecord from table slice, but also all records from tablestransactionand classification_itemreferencing it:

   DELETE FROM slice WHERE name='my_slice';
   

1. 删除并重新创建约束以添加 CASCADE 删除：

   ALTER TABLE ONLY "transaction" 
       DROP CONSTRAINT transaction_slice_id_fkey;
   ALTER TABLE ONLY "transaction" 
       ADD CONSTRAINT transaction_slice_id_fkey 
       FOREIGN KEY (slice_id) REFERENCES slice(id) ON DELETE CASCADE;
   
   ALTER TABLE ONLY "classification_item" 
       DROP CONSTRAINT classification_item_transaction_id_fkey;
   ALTER TABLE ONLY "classification_item" 
       ADD CONSTRAINT classification_item_transaction_id_fkey 
       FOREIGN KEY (transaction_id) REFERENCES transaction(id) ON DELETE CASCADE;
   

2. 现在，以下查询不仅会删除my_slicetable 中的记录，还会删除table 中的slice所有记录transaction并classification_item引用它：

   DELETE FROM slice WHERE name='my_slice';
   

That procedure will work even if the original schema is created by an object-relational mapper like SQLAlchemy. However in such a case, take care to re-apply that "patch" whenever the schema changes or is re-created. Only if that can't be implemented automatically, it might not be a good idea after all …

即使原始模式是由像 SQLAlchemy 这样的对象关系映射器创建的，该过程也将起作用。但是，在这种情况下，请注意在架构更改或重新创建时重新应用该“补丁”。只有当它不能自动实现时，它可能毕竟不是一个好主意……

回答by Oleg Tatarchuk

It can be delegated to DBMS by set a constraint property 'On delete' = CASCADE. Please see an example.

可以通过设置约束属性“On delete”= CASCADE 将其委托给 DBMS。请看一个例子。