PostgreSQL 中的级联删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cascading deletes in PostgreSQL
提问by
I have a database with a few dozen tables interlinked with foreign keys. Under normal circumstances, I want the default ON DELETE RESTRICTbehavior 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 CASCADEcommand hadn't been pure hallucination.
我有一个数据库，里面有几十个与外键相连的表。在正常情况下，我想要ON DELETE RESTRICT这些约束的默认行为。但是在尝试与顾问共享数据库快照时，我需要删除一些敏感数据。我希望我对DELETE FROM Table CASCADE命令的记忆不是纯粹的幻觉。
What I ended out doing was dumping the database, writing a script to process the dump by adding ON DELETE CASCADEclauses 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.
我最终做的是转储数据库，编写一个脚本来处理转储，通过添加ON DELETE CASCADE子句和所有外键约束，从中恢复，执行我的删除，再次转储，删除ON DELETE CASCADE，最后再次恢复。这比编写我需要在 SQL 中执行此操作的删除查询更容易——删除数据库的整个切片不是正常操作，因此模式并没有完全适应它。
Does anyone have a better solution for the next time something like this comes up?
下次出现这样的事情时，有人有更好的解决方案吗？
回答by Grant Johnson
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;
回答by agnul
You could create the foreign key constraints as DEFERRABLE. Then you would be able to temporarily disable them while you scrub the data and re-enable them when you are done. Have a look at this question.
您可以将外键约束创建为 DEFERRABLE。然后，您可以在清理数据时暂时禁用它们，并在完成后重新启用它们。看看这个问题。
回答by user362911
TRUNCATE table CASCADE;
I'm a Postgres novice, so I'm not sure what the trade-off is for TRUNCATE vs. DROP.
我是 Postgres 新手，所以我不确定 TRUNCATE 与 DROP 的权衡是什么。
回答by Tim Davis
TRUNCATE just removes the data from table and leaves the structure
TRUNCATE 只是从表中删除数据并离开结构
回答by Tony Lenzi
You may want to look into using schemaswith PostgreSQL. I've done this in past projects to allow different groups of people or developers to have their own data. Then you can use your scripts to create multiple copies of your database for just such situations.
您可能希望研究在PostgreSQL 中使用模式。我在过去的项目中这样做是为了让不同的人或开发人员拥有自己的数据。然后，您可以使用脚本为此类情况创建数据库的多个副本。
回答by Tony Lenzi
@Tony: No, schemas can be useful, and indeed, we use them to partition data in our database. But I'm talking about trying to scrub sensitive data before letting a consultant have a copy of the db. I want that data gone.
@Tony：不，模式很有用，事实上，我们使用它们来对数据库中的数据进行分区。但我说的是在让顾问拥有数据库副本之前尝试清理敏感数据。我想要那个数据消失。
回答by angch
I don't think you'd need to process the dump file like that. Do a streaming dump/restore, and process that. Something like:
我认为您不需要像那样处理转储文件。进行流式转储/恢复，并进行处理。就像是：
createdb -h scratchserver scratchdb
createdb -h scratchserver sanitizeddb
pg_dump -h liveserver livedb --schema-only | psql -h scratchserver sanitizeddb
pg_dump -h scratchserver sanitizeddb | sed -e "s/RESTRICT/CASCADE/" | psql -h scratchserver scratchdb
pg_dump -h liveserver livedb --data-only | psql -h scratchserver scratchdb
psql -h scrachserver scratchdb -f delete-sensitive.sql
pg_dump -h scratchserver scratchdb --data-only | psql -h scratchserver sanitizeddb
pg_dump -Fc -Z9 -h scratchserver sanitizedb > sanitizeddb.pgdump
where you store all your DELETE sqls in delete-sensitive.sql. The sanitizeddb database/steps can be removed if you don't mind the consultant getting a db with CASCADE foreign keys instead of RESTRICT foreign keys.
您将所有 DELETE sql 存储在 delete-sensitive.sql 中的位置。如果您不介意顾问使用 CASCADE 外键而不是 RESTRICT 外键获取数据库，则可以删除 sanitizeddb 数据库/步骤。
There might also be better ways depending on how often you need to do this, how big the database is, and what percentage of data is sensitive, but I can't think of a simpler way to do it once or twicefor a reasonably sizeddatabase. You'd need a different database after all, so unless you already have a slony cluster, can't avoid the dump/restore cycle, which might be time consuming.
根据您需要执行此操作的频率、数据库有多大以及敏感数据的百分比，可能还有更好的方法，但我想不出一种更简单的方法来为合理大小的用户执行一次或两次数据库。毕竟你需要一个不同的数据库，所以除非你已经有一个slony集群，否则无法避免转储/恢复周期，这可能很耗时。
