级联删除一次

我有一个Postgresql数据库，我想做一些级联删除。但是，这些表不是用ON DELETE CASCADE规则设置的。有没有办法我可以执行一个删除，并告诉Postgresql级联它只是这一次?等价于

DELETE FROM some_table CASCADE;

这个老问题的答案似乎不存在这样的解决方案，但我想我要明确地问这个问题，只是为了确定。

522822 次浏览
小开
最佳答案

不。要做到这一点，只需为要级联的表编写delete语句。

DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table);
DELETE FROM some_table;
小开

带cascade选项的删除只应用于定义了外键的表。如果您执行删除操作，而它告诉您不能删除，因为这会违反外键约束，级联将导致它删除违规行。

如果希望以这种方式删除关联的行，则需要首先定义外键。另外，请记住，除非您显式地指示它开始一个事务，或者更改默认值，否则它将进行自动提交，清理这可能非常耗时。

小开

如果我理解正确的话，您应该能够通过删除外键约束、添加一个新的外键约束(它将级联)、做您的事情和重新创建限制外键约束来做您想做的事情。

例如:

testing=# create table a (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
testing=# create table b (id integer references a);
CREATE TABLE


-- put some data in the table
testing=# insert into a values(1);
INSERT 0 1
testing=# insert into a values(2);
INSERT 0 1
testing=# insert into b values(2);
INSERT 0 1
testing=# insert into b values(1);
INSERT 0 1


-- restricting works
testing=# delete from a where id=1;
ERROR:  update or delete on table "a" violates foreign key constraint "b_id_fkey" on table "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".


-- find the name of the constraint
testing=# \d b;
Table "public.b"
Column |  Type   | Modifiers
--------+---------+-----------
id     | integer |
Foreign-key constraints:
"b_id_fkey" FOREIGN KEY (id) REFERENCES a(id)


-- drop the constraint
testing=# alter table b drop constraint b_a_id_fkey;
ALTER TABLE


-- create a cascading one
testing=# alter table b add FOREIGN KEY (id) references a(id) on delete cascade;
ALTER TABLE


testing=# delete from a where id=1;
DELETE 1
testing=# select * from a;
id
----
2
(1 row)


testing=# select * from b;
id
----
2
(1 row)


-- it works, do your stuff.
-- [stuff]


-- recreate the previous state
testing=# \d b;
Table "public.b"
Column |  Type   | Modifiers
--------+---------+-----------
id     | integer |
Foreign-key constraints:
"b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) ON DELETE CASCADE


testing=# alter table b drop constraint b_id_fkey;
ALTER TABLE
testing=# alter table b add FOREIGN KEY (id) references a(id) on delete restrict;
ALTER TABLE

当然，为了你的心理健康，你应该把这样的东西抽象成一个程序。

小开

如果你真的想要 DELETE FROM some_table CASCADE;意思是“删除表some_table中的所有行”，你可以使用TRUNCATE代替DELETE，并且始终支持CASCADE。然而，如果你想使用带有where子句的选择性删除，TRUNCATE是不够的。

小心使用 -这将会在some_table上有外键约束的删除所有表的所有行和所有在这些表上有约束的表，等等。

Postgres支持CASCADE和截断命令:

TRUNCATE some_table CASCADE;

这是事务性的(即可以回滚)，尽管它没有完全与其他并发事务隔离，并且有其他几个注意事项。详细信息请阅读文档。

小开
我写了一个(递归)函数，根据它的主键删除任何行。我写这个是因为我不想创建我的约束作为“删除级联”。我希望能够删除复杂的数据集(作为一名DBA)，但不允许我的程序员能够在不考虑所有影响的情况下进行级联删除。 我还在测试这个功能，所以可能有bug在它-但请不要尝试，如果你的DB有多列主键(因此外键)。同样，键都必须能够以字符串形式表示，但它可以用一种没有这种限制的方式来写。无论如何，我非常谨慎地使用这个函数，我太重视我的数据了，以至于不能对所有东西启用级联约束。 基本上，这个函数是在模式、表名和主值(以字符串形式)中传递的，它将首先查找该表上的任何外键，并确保数据不存在——如果存在，它将根据找到的数据递归地调用自己。它使用一个已经标记为删除的数据数组来防止无限循环。请测试一下，让我知道它是如何为您工作的。注意:有点慢。 我这样称呼它: select delete_cascade('public','my_table','1'); < / p > 
create or replace function delete_cascade(p_schema varchar, p_table varchar, p_key varchar, p_recursion varchar[] default null)
returns integer as $$
declare
rx record;
rd record;
v_sql varchar;
v_recursion_key varchar;
recnum integer;
v_primary_key varchar;
v_rows integer;
begin
recnum := 0;
select ccu.column_name into v_primary_key
from
information_schema.table_constraints  tc
join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
and tc.constraint_type='PRIMARY KEY'
and tc.table_name=p_table
and tc.table_schema=p_schema;


for rx in (
select kcu.table_name as foreign_table_name,
kcu.column_name as foreign_column_name,
kcu.table_schema foreign_table_schema,
kcu2.column_name as foreign_table_primary_key
from information_schema.constraint_column_usage ccu
join information_schema.table_constraints tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema
join information_schema.key_column_usage kcu on kcu.constraint_name=ccu.constraint_name and kcu.constraint_catalog=ccu.constraint_catalog and kcu.constraint_schema=ccu.constraint_schema
join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
join information_schema.key_column_usage kcu2 on kcu2.constraint_name=tc2.constraint_name and kcu2.constraint_catalog=tc2.constraint_catalog and kcu2.constraint_schema=tc2.constraint_schema
where ccu.table_name=p_table  and ccu.table_schema=p_schema
and TC.CONSTRAINT_TYPE='FOREIGN KEY'
and tc2.constraint_type='PRIMARY KEY'
)
loop
v_sql := 'select '||rx.foreign_table_primary_key||' as key from '||rx.foreign_table_schema||'.'||rx.foreign_table_name||'
where '||rx.foreign_column_name||'='||quote_literal(p_key)||' for update';
--raise notice '%',v_sql;
--found a foreign key, now find the primary keys for any data that exists in any of those tables.
for rd in execute v_sql
loop
v_recursion_key=rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name||'='||rd.key;
if (v_recursion_key = any (p_recursion)) then
--raise notice 'Avoiding infinite loop';
else
--raise notice 'Recursing to %,%',rx.foreign_table_name, rd.key;
recnum:= recnum +delete_cascade(rx.foreign_table_schema::varchar, rx.foreign_table_name::varchar, rd.key::varchar, p_recursion||v_recursion_key);
end if;
end loop;
end loop;
begin
--actually delete original record.
v_sql := 'delete from '||p_schema||'.'||p_table||' where '||v_primary_key||'='||quote_literal(p_key);
execute v_sql;
get diagnostics v_rows= row_count;
--raise notice 'Deleting %.% %=%',p_schema,p_table,v_primary_key,p_key;
recnum:= recnum +v_rows;
exception when others then recnum=0;
end;


return recnum;
end;
$$
language PLPGSQL;
小开
你可以使用ON DELETE CASCADE定义外键约束来实现自动化 我引用外键约束手册:

CASCADE指定当删除引用行的时候，row(s) 引用它也会被自动删除

小开
我不能评论帕勒霍斯的答案，所以我加上了我自己的答案。 paleshorse的逻辑是可以的，但大数据集的效率可能很差 
DELETE FROM some_child_table sct
WHERE exists (SELECT FROM some_Table st
WHERE sct.some_fk_fiel=st.some_id);


DELETE FROM some_table;

如果列上有索引，数据集比少数记录大，那么速度会更快。

小开

我拿了Joe Love的答案，用带有子选择的IN操作符重写了它，而不是=，以使函数更快(根据Hubbitus的建议):

create or replace function delete_cascade(p_schema varchar, p_table varchar, p_keys varchar, p_subquery varchar default null, p_foreign_keys varchar[] default array[]::varchar[])
returns integer as $$
declare


rx record;
rd record;
v_sql varchar;
v_subquery varchar;
v_primary_key varchar;
v_foreign_key varchar;
v_rows integer;
recnum integer;


begin


recnum := 0;
select ccu.column_name into v_primary_key
from
information_schema.table_constraints  tc
join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
and tc.constraint_type='PRIMARY KEY'
and tc.table_name=p_table
and tc.table_schema=p_schema;


for rx in (
select kcu.table_name as foreign_table_name,
kcu.column_name as foreign_column_name,
kcu.table_schema foreign_table_schema,
kcu2.column_name as foreign_table_primary_key
from information_schema.constraint_column_usage ccu
join information_schema.table_constraints tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema
join information_schema.key_column_usage kcu on kcu.constraint_name=ccu.constraint_name and kcu.constraint_catalog=ccu.constraint_catalog and kcu.constraint_schema=ccu.constraint_schema
join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
join information_schema.key_column_usage kcu2 on kcu2.constraint_name=tc2.constraint_name and kcu2.constraint_catalog=tc2.constraint_catalog and kcu2.constraint_schema=tc2.constraint_schema
where ccu.table_name=p_table  and ccu.table_schema=p_schema
and TC.CONSTRAINT_TYPE='FOREIGN KEY'
and tc2.constraint_type='PRIMARY KEY'
)
loop
v_foreign_key := rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name;
v_subquery := 'select "'||rx.foreign_table_primary_key||'" as key from '||rx.foreign_table_schema||'."'||rx.foreign_table_name||'"
where "'||rx.foreign_column_name||'"in('||coalesce(p_keys, p_subquery)||') for update';
if p_foreign_keys @> ARRAY[v_foreign_key] then
--raise notice 'circular recursion detected';
else
p_foreign_keys := array_append(p_foreign_keys, v_foreign_key);
recnum:= recnum + delete_cascade(rx.foreign_table_schema, rx.foreign_table_name, null, v_subquery, p_foreign_keys);
p_foreign_keys := array_remove(p_foreign_keys, v_foreign_key);
end if;
end loop;


begin
if (coalesce(p_keys, p_subquery) <> '') then
v_sql := 'delete from '||p_schema||'."'||p_table||'" where "'||v_primary_key||'"in('||coalesce(p_keys, p_subquery)||')';
--raise notice '%',v_sql;
execute v_sql;
get diagnostics v_rows = row_count;
recnum := recnum + v_rows;
end if;
exception when others then recnum=0;
end;


return recnum;


end;
$$
language PLPGSQL;
小开

是的，正如其他人所说，没有方便的“DELETE FROM my_table…”CASCADE'(或等效)。要删除非级联外键保护的子记录及其引用的祖先，您的选项包括:

  • 显式地执行所有的删除，一次一个查询，从子表开始(尽管如果你有循环引用，这将无法运行);或
  • 在单个(潜在的大量)查询中显式执行所有删除;或
  • 假设你的非级联外键约束被创建为'ON DELETE NO ACTION DEFERRABLE'，在一个事务中显式地执行所有删除;或
  • 暂时删除图中的“no action”和“restrict”外键约束，将它们重新创建为CASCADE，删除违规的祖先，再次删除外键约束，最后重新创建它们，就像它们原来的样子(因此暂时削弱了数据的完整性);或
  • 一些同样有趣的事情。

我想，绕过外键约束是故意不方便的;但我能理解为什么在特定情况下你想这么做。如果这是您经常要做的事情，并且您愿意无视dba的智慧，那么您可能希望通过一个过程将其自动化。

几个月前，我来到这里寻找“CASCADE DELETE only once”问题的答案(最初在十多年前被问到!)。我从Joe Love的聪明的解决方案(以及Thomas C. G. de Vilhena的变体)中获得了一些好处，但最终我的用例有特殊的需求(例如处理表内循环引用)，这迫使我采取不同的方法。这种方法最终成为recursively_delete (PG 10.10)。

我已经在生产环境中使用recursively_delete有一段时间了，现在，我终于有足够的信心(谨慎地)将它提供给其他可能在这里寻找想法的人。与Joe Love的解决方案一样，它允许你删除整个数据图，就像你数据库中的所有外键约束都被暂时设置为CASCADE一样，但它提供了一些额外的功能:

  • 提供删除目标及其图形的ASCII预览 李家属。< / >
  • 使用递归cte在单个查询中执行删除。
  • 处理表内和表间的循环依赖关系。
  • 处理复合键。
  • 跳过'set default'和'set null'约束。

提交答案