postgresql 级联删除一次

CASCADE DELETE just once

提问by Eli Courtwright

I have a Postgresql database on which I want to do a few cascading deletes. However, the tables aren't set up with the ON DELETE CASCADE rule. Is there any way I can perform a delete and tell Postgresql to cascade it just this once? Something equivalent to

我有一个 Postgresql 数据库，我想对其进行一些级联删除。但是，表不是使用 ON DELETE CASCADE 规则设置的。有什么方法可以执行删除并告诉 Postgresql 将它级联一次？相当于

DELETE FROM some_table CASCADE;

The answers to this older questionmake it seem like no such solution exists, but I figured I'd ask this question explicitly just to be sure.

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

采纳答案by palehorse

No. To do it just once you would simply write the delete statement for the table you want to cascade.

不需要。只需为要级联的表编写删除语句即可。

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

回答by DanC

If you really wantDELETE FROM some_table CASCADE;which means "remove all rows from table some_table", you can use TRUNCATEinstead of DELETEand CASCADEis always supported. However, if you want to use selective delete with a whereclause, TRUNCATEis not good enough.

如果您真的想要DELETE FROM some_table CASCADE;这意味着“从表中删除所有行some_table”，则可以使用TRUNCATE代替DELETE并且CASCADE始终受支持。但是，如果要使用带有where子句的选择性删除，TRUNCATE还不够好。

USE WITH CARE- This will drop all rows of all tableswhich have a foreign key constraint on some_tableand all tables that have constraints on those tables, etc.

小心使用- 这将删除所有具有外键约束的表的所​​有行以及some_table对这些表具有约束的所有表等。

Postgres supports CASCADEwith TRUNCATE command:

Postgres的支持CASCADE与TRUNCATE命令：

TRUNCATE some_table CASCADE;

Handily this is transactional (i.e. can be rolled back), although it is not fully isolated from other concurrent transactions, and has several other caveats. Read the docs for details.

方便的是，这是事务性的（即可以回滚），尽管它没有与其他并发事务完全隔离，并且还有其他一些注意事项。阅读文档了解详细信息。

回答by Joe Love

I wrote a (recursive) function to delete any row based on its primary key. I wrote this because I did not want to create my constraints as "on delete cascade". I wanted to be able to delete complex sets of data (as a DBA) but not allow my programmers to be able to cascade delete without thinking through all of the repercussions. I'm still testing out this function, so there may be bugs in it -- but please don't try it if your DB has multi column primary (and thus foreign) keys. Also, the keys all have to be able to be represented in string form, but it could be written in a way that doesn't have that restriction. I use this function VERY SPARINGLY anyway, I value my data too much to enable the cascading constraints on everything. Basically this function is passed in the schema, table name, and primary value (in string form), and it will start by finding any foreign keys on that table and makes sure data doesn't exist-- if it does, it recursively calls itsself on the found data. It uses an array of data already marked for deletion to prevent infinite loops. Please test it out and let me know how it works for you. Note: It's a little slow. I call it like so: select delete_cascade('public','my_table','1');

我编写了一个（递归）函数来根据主键删除任何行。我写这个是因为我不想将我的约束创建为“删除级联”。我希望能够删除复杂的数据集（作为 DBA），但不允许我的程序员在不考虑所有影响的情况下进行级联删除。我仍在测试此功能，因此其中可能存在错误 - 但如果您的数据库具有多列主键（以及外键），请不要尝试。此外，所有键都必须能够以字符串形式表示，但它可以以没有这种限制的方式编写。无论如何，我非常谨慎地使用此函数，我非常重视我的数据，以至于无法对所有内容启用级联约束。基本上这个函数是通过模式、表名和主值（以字符串形式）传递的，它将首先查找该表上的任何外键，并确保数据不存在——如果存在，它会在找到的数据上递归调用自己。它使用已标记为删除的数据数组来防止无限循环。请测试一下，让我知道它是如何为您工作的。注意：它有点慢。我这样称呼它： select delete_cascade('public','my_table','1');

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;

回答by Ryszard Szopa

If I understand correctly, you should be able to do what you want by dropping the foreign key constraint, adding a new one (which will cascade), doing your stuff, and recreating the restricting foreign key constraint.

如果我理解正确，您应该能够通过删除外键约束、添加一个新约束（将级联）、做您的工作并重新创建限制性外键约束来做您想做的事情。

For example:

例如：

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

Of course, you should abstract stuff like that into a procedure, for the sake of your mental health.

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

回答by TRL

Yeah, as others have said, there's no convenient 'DELETE FROM my_table ... CASCADE' (or equivalent). To delete non-cascading foreign key-protected child records and their referenced ancestors, your options include:

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

• Perform all the deletions explicitly, one query at a time, starting with child tables (though this won't fly if you've got circular references); or
• Perform all the deletions explicitly in a single (potentially massive) query; or
• Assuming your non-cascading foreign key constraints were created as 'ON DELETE NO ACTION DEFERRABLE', perform all the deletions explicitly in a single transaction; or
• Temporarily drop the 'no action' and 'restrict' foreign key constraints in the graph, recreate them as CASCADE, delete the offending ancestors, drop the foreign key constraints again, and finally recreate them as they were originally (thus temporarily weakening the integrity of your data); or
• Something probably equally fun.

• 显式执行所有删除操作，一次一个查询，从子表开始（尽管如果您有循环引用，这不会成功）；或者
• 在单个（可能是大量）查询中显式执行所有删除；或者
• 假设您的非级联外键约束被创建为“ON DELETE NO ACTION DEFERRABLE”，在单个事务中显式执行所有删除；或者
• 暂时删除图中的“no action”和“restrict”外键约束，将它们重新创建为 CASCADE，删除违规的祖先，再次删除外键约束，最后重新创建它们原来的样子（从而暂时削弱了您的数据）；或者
• 一些东西可能同样有趣。

It's on purpose that circumventing foreign key constraints isn't made convenient, I assume; but I do understand why in particular circumstances you'd want to do it. If it's something you'll be doing with some frequency, and if you're willing to flout the wisdom of DBAs everywhere, you may want to automate it with a procedure.

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

I came here a few months ago looking for an answer to the "CASCADE DELETE just once" question (originally asked over a decade ago!). I got some mileage out of Joe Love's clever solution (and Thomas C. G. de Vilhena's variant), but in the end my use case had particular requirements (handling of intra-table circular references, for one) that forced me to take a different approach. That approach ultimately became recursively_delete(PG 10.10).

几个月前我来到这里寻找“仅一次级联删除”问题的答案（最初是在十多年前提出的！）。我从 Joe Love 的聪明解决方案（以及 Thomas CG de Vilhena 的变体）中获得了一些进展，但最终我的用例有特殊要求（例如处理表内循环引用），这迫使我采取不同的方法。这种方法最终变成了recursively_delete(PG 10.10)。

I've been using recursively_delete in production for a while, now, and finally feel (warily) confident enough to make it available to others who might wind up here looking for ideas. As with Joe Love's solution, it allows you to delete entire graphs of data as if all foreign key constraints in your database were momentarily set to CASCADE, but offers a couple additional features:

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

• Provides an ASCII preview of the deletion target and its graph of dependents.
• Performs deletion in a single query using recursive CTEs.
• Handles circular dependencies, intra- and inter-table.
• Handles composite keys.
• Skips 'set default' and 'set null' constraints.

• 提供删除目标及其依赖关系图的 ASCII 预览。
• 使用递归 CTE 在单个查询中执行删除。
• 处理循环依赖、表内和表间。
• 处理复合键。
• 跳过“设置默认值”和“设置空值”约束。

回答by Grzegorz Grabek

I cannot comment Palehorse's answer so I added my own answer. Palehorse's logic is ok but efficiency can be bad with big data sets.

我无法评论 Palehorse 的答案，所以我添加了我自己的答案。Palehorse 的逻辑没问题，但大数据集的效率可能很差。

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;

It is faster if you have indexes on columns and data set is bigger than few records.

如果您在列上有索引并且数据集大于几条记录，则速度会更快。

回答by atiruz

You can use to automate this, you could define the foreign key constraint with ON DELETE CASCADE.
I quote the the manual of foreign key constraints:

您可以使用自动执行此操作，您可以使用ON DELETE CASCADE.
我引用了外键约束手册：

CASCADEspecifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

CASCADE指定当删除引用的行时，引用它的行也应自动删除。

回答by Thomas C. G. de Vilhena

I took Joe Love's answer and rewrote it using the INoperator with sub-selects instead of =to make the function faster (according to Hubbitus's suggestion):

我接受了 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;

回答by Grant Johnson

The delete with the cascade option only applied to tables with foreign keys defined. If you do a delete, and it says you cannot because it would violate the foreign key constraint, the cascade will cause it to delete the offending rows.

带有级联选项的删除仅适用于定义了外键的表。如果您执行删除操作，并且它说您不能，因为它会违反外键约束，级联将导致它删除有问题的行。

If you want to delete associated rows in this way, you will need to define the foreign keys first. Also, remember that unless you explicitly instruct it to begin a transaction, or you change the defaults, it will do an auto-commit, which could be very time consuming to clean up.

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