我有一个数据库,其中有几十个表与外键相互关联.在正常情况下,我希望这些约束的默认ON DELETE RESTRICT行为.但是当我尝试与顾问共享数据库的快照时,我需要删除一些敏感数据.我希望我对DELETE FROM Table CASCADE命令的记忆不是纯粹的幻觉.
我最后做的是转储数据库,编写脚本来处理转储,通过添加ON DELETE CASCADE子句以及所有外键约束,从中恢复,执行删除,再次转储,删除ON DELETE CASCADE,最后恢复再次.这比编写我在sql中需要执行此操作所需的删除查询更容易 – 删除数据库的整个切片不是正常操作,因此架构并不完全适应它.
有没有人有更好的解决方案,下次这样的东西出现？
您不需要转储和还原.您应该能够删除约束,使用级联重建它,删除它,再次删除它,并使用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;
