在PostgreSQL中级联删除 [英] Cascading deletes in PostgreSQL

问题描述

我有一个数据库，其中有几十个与外键链接的表。通常情况下，我希望这些约束具有默认的 ON DELETE RESTRICT 行为。但是，当尝试与顾问共享数据库快照时，我需要删除一些敏感数据。我希望我对从表CASCADE删除命令的记忆不是纯粹的幻觉。

I have a database with a few dozen tables interlinked with foreign keys. Under normal circumstances, I want the default ON DELETE RESTRICT behavior for those constraints. But when trying to share a snapshot of the database with a consultant, I needed to remove some sensitive data. I wish that my memory of a DELETE FROM Table CASCADE command hadn't been pure hallucination.

我结束了什么要做的就是转储数据库，编写脚本来处理转储，方法是在所有外键约束中添加 ON DELETE CASCADE 子句，从中恢复，执行删除，然后再次转储，删除 ON DELETE CASCADE ，最后再次恢复。这比编写用SQL来执行删除查询所需要的查询要容易得多，删除查询的整个部分都不是正常的操作，因此架构并不完全适合它。

What I ended out doing was dumping the database, writing a script to process the dump by adding ON DELETE CASCADE clauses too all the foreign key constraints, restoring from that, performing my deletes, dumping again, removing the ON DELETE CASCADE, and finally restoring again. That was easier than writing the deletion query I'd have needed to do this in SQL -- removing whole slices of the database isn't a normal operation, so the schema isn't exactly adapted to it.

下次有人会遇到更好的解决方案吗？

Does anyone have a better solution for the next time something like this comes up?

推荐答案

您不需要转储和还原。您应该能够删除约束，使用级联重建约束，执行删除操作，再次删除约束，然后使用strict重建约束。

You do not need to dump and restore. You should be able to just drop the constraint, rebuild it with cascade, do your deletes, drop it again, and the rebuild it with restrict.

CREATE TABLE "header"
(
  header_id serial NOT NULL,
  CONSTRAINT header_pkey PRIMARY KEY (header_id)
);

CREATE TABLE detail
(
  header_id integer,
  stuff text,
  CONSTRAINT detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
insert into header values(1);
insert into detail values(1,'stuff');
delete from header where header_id=1;
alter table detail drop constraint detail_header_id_fkey;
alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) on delete cascade;
delete from header where header_id=1;
alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) on delete restrict;

这篇关于在PostgreSQL中级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！