首页 / 知识

关于sql：在PostgreSQL中级联删除

2023-04-14 07:34:00

Cascading deletes in PostgreSQL

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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;

1	TRUNCATE TABLE CASCADE;

我是Postgres的新手，所以不确定TRUNCATE与DROP之间的权衡是什么。

您可以将外键约束创建为DEFERRABLE。然后，您可以在清理数据时临时禁用它们，并在完成后重新启用它们。看看这个问题。

TRUNCATE只是从表中删除数据并保留结构

我认为您不需要像这样处理转储文件。执行流转储/还原，并进行处理。就像是：

1
2
3
4
5
6
7
8
9
10
11

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

您将所有DELETE sql存储在delete-sensitive.sql中的位置。如果您不介意顾问使用CASCADE外键而不是RESTRICT外键获取数据库，则可以删除sanitizeddb数据库/步骤。

可能还有更好的方法，具体取决于您需要执行此操作的频率，数据库的大小以及敏感数据的百分比，但是我想不出一种简单的方法来对合理大小的数据库执行一次或两次数据库。毕竟，您将需要一个不同的数据库，因此，除非您已经拥有一个笨拙的集群，否则无法避免转储/恢复周期，这可能会很耗时。

@Tony：不，模式可能有用，实际上，我们使用它们来对数据库中的数据进行分区。但是我说的是在让顾问拥有数据库副本之前尝试清理敏感数据。我希望这些数据消失。

您可能想研究在PostgreSQL中使用模式。在过去的项目中，我已经这样做过，以允许不同的人群或开发人员拥有自己的数据。然后，您可以使用脚本为这种情况创建数据库的多个副本。

级联删除数据库链接外键