如何为postgres编写删除级联？
我正在为postgres手动构造一个DELETE CASCADE语句。
我有一个'transaction‘和一个'slice’表，相关如下所示：
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_ classification_items’的切片引用的所有事务和切片。我需要写些什么？
=# delete from classification_item where transaction_id= #...?
=# delete from transaction where slice_id= #...?
=# delete from slice where name='my_slice';回答 5
Stack Overflow用户
发布于 2010-09-15 04:04:35
以防你不能按照别人的建议去做：
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;Stack Overflow用户
发布于 2010-09-15 03:58:01
Postgres外键支持级联删除：
slice_id integer REFERENCES slice(id) ON DELETE CASCADE等
Stack Overflow用户
发布于 2016-12-11 23:50:04
您应该使用级联删除，即使您继承了数据库架构，也应该可以这样做。您只需修改约束，将级联删除添加到模式中：
- 删除并重新创建约束以添加级联删除：
ALTER TABLE ONLY "transaction“DROP CONSTRAINT transaction_slice_id_fkey；ALTER TABLE ONLY "transaction”在DELETE CASCADE上添加约束transaction_slice_id_fkey外键(slice_id)引用切片(Id)；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)在DELETE CASCADE上引用事务(Id)；
- 现在，以下查询不仅会从表
slice中删除my_slice记录，还会从表transaction和classification_item中删除引用该记录的所有记录：
从切片中删除WHERE name='my_slice';
即使原始模式是由像SQLAlchemy这样的对象关系映射器创建的，这个过程也是有效的。但是，在这种情况下，请注意在模式更改或重新创建时重新应用“补丁程序”。只有在不能自动实现的情况下，这毕竟不是一个好主意…
https://stackoverflow.com/questions/3711580
复制相似问题
腾讯云开发者
