blocks|key|1743612|text|不是的。要只做一次，只需为要级联的表编写delete语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1743613|DELETE+FROM+some_child_table+WHERE+some_fk_field+IN+(SELECT+some_id+FROM+some_Table);
DELETE+FROM+some_table;|code-block|syntax|javascript|1743614|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table);
DELETE FROM some_table;
</code></pre>

blocks|key|1528650|text|delete+with+cascade选项仅适用于定义了外键的表。如果您执行了delete操作，并且系统提示您不能删除，因为这将违反外键约束，则级联将导致它删除有问题的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528651|如果要以这种方式删除关联的行，则需要首先定义外键。此外，请记住，除非您显式指示它开始一个事务，或者您更改了默认值，否则它将执行自动提交，这可能非常耗时地进行清理。|1528652|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1743714|text|如果我理解正确的话，您应该能够通过删除外键约束、添加新的外键约束(将级联)、做自己的事情并重新创建限制外键约束来做您想做的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1743715|例如：|1743716|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+%7C++Type+++%7C+Modifiers+
--------%2B---------%2B-----------
+id+++++%7C+integer+%7C+
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+%7C++Type+++%7C+Modifiers+
--------%2B---------%2B-----------
+id+++++%7C+integer+%7C+
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|code-block|syntax|javascript|1743717|当然，为了你的心理健康，你应该将这样的东西抽象到一个过程中。|1743718|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>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
</code></pre>

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

blocks|key|1743773|text|我编写了一个(递归)函数来删除任何基于主键的行。我写这篇文章是因为我不想把我的约束创建为"on+delete+cascade“。我希望能够删除复杂的数据集(作为DBA)，但不允许我的程序员在不考虑所有影响的情况下级联删除。我仍然在测试这个函数，所以其中可能有bugs+-但是如果您的DB有多列主键(以及外键)，请不要尝试它。此外，所有的键都必须能够以字符串的形式表示，但它可以以一种没有这种限制的方式来编写。无论如何，我非常谨慎地使用这个函数，我太看重我的数据而不能在所有东西上启用级联约束。基本上，此函数传入模式、表名和主值(以字符串形式)，它将首先查找该表上的任何外键，并确保数据不存在--如果存在，则递归地对找到的数据调用自身。它使用已标记为删除的数据数组来防止无限循环。请测试它，并让我知道它是如何为您工作的。注意:它有点慢。我这样叫它：select+delete_cascade('public','my_table','1');|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1743774|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+'%7C%7Crx.foreign_table_primary_key%7C%7C'+as+key+from+'%7C%7Crx.foreign_table_schema%7C%7C'.'%7C%7Crx.foreign_table_name%7C%7C'
++++++++++++where+'%7C%7Crx.foreign_column_name%7C%7C'='%7C%7Cquote_literal(p_key)%7C%7C'+for+update';
++++++++--raise+notice+'%25',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%7C%7C'.'%7C%7Crx.foreign_table_name%7C%7C'.'%7C%7Crx.foreign_column_name%7C%7C'='%7C%7Crd.key;
++++++++++++if+(v_recursion_key+=+any+(p_recursion))+then
++++++++++++++++--raise+notice+'Avoiding+infinite+loop';
++++++++++++else
++++++++++++++++--raise+notice+'Recursing+to+%25,%25',rx.foreign_table_name,+rd.key;
++++++++++++++++recnum:=+recnum+%2Bdelete_cascade(rx.foreign_table_schema::varchar,+rx.foreign_table_name::varchar,+rd.key::varchar,+p_recursion%7C%7Cv_recursion_key);
++++++++++++end+if;
++++++++end+loop;
++++end+loop;
++++begin
++++--actually+delete+original+record.
++++v_sql+:=+'delete+from+'%7C%7Cp_schema%7C%7C'.'%7C%7Cp_table%7C%7C'+where+'%7C%7Cv_primary_key%7C%7C'='%7C%7Cquote_literal(p_key);
++++execute+v_sql;
++++get+diagnostics+v_rows=+row_count;
++++--raise+notice+'Deleting+%25.%25+%25=%25',p_schema,p_table,v_primary_key,p_key;
++++recnum:=+recnum+%2Bv_rows;
++++exception+when+others+then+recnum=0;
++++end;

++++return+recnum;
end;
$$
language+PLPGSQL;|code-block|syntax|javascript|1743775|entityMap^0|AD|1B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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:
<code>select delete_cascade('public','my_table','1');</code>

<pre><code>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;
</code></pre>

blocks|key|1528812|text|我不能评论Palehorse的答案，所以我添加了我自己的答案。帕莱马的逻辑是可以的，但对于大数据集来说，效率可能会很差。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528813|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;|code-block|syntax|javascript|1528814|如果你在列上有索引，并且数据集比几条记录要大，速度会更快。|1528815|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>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;
</code></pre>

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

blocks|key|1528861|text|我接受了Joe+Love的回答，并使用带有子选择的IN操作符而不是=来重写它，以使函数更快(根据Hubbitus的建议)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1528862|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%7C%7C'.'%7C%7Crx.foreign_table_name%7C%7C'.'%7C%7Crx.foreign_column_name;
++++++++v_subquery+:=+'select+"'%7C%7Crx.foreign_table_primary_key%7C%7C'"+as+key+from+'%7C%7Crx.foreign_table_schema%7C%7C'."'%7C%7Crx.foreign_table_name%7C%7C'"
+++++++++++++where+"'%7C%7Crx.foreign_column_name%7C%7C'"in('%7C%7Ccoalesce(p_keys,+p_subquery)%7C%7C')+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+%2B+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+'%7C%7Cp_schema%7C%7C'."'%7C%7Cp_table%7C%7C'"+where+"'%7C%7Cv_primary_key%7C%7C'"in('%7C%7Ccoalesce(p_keys,+p_subquery)%7C%7C')';
++++++++++++--raise+notice+'%25',v_sql;
++++++++++++execute+v_sql;
++++++++++++get+diagnostics+v_rows+=+row_count;
++++++++++++recnum+:=+recnum+%2B+v_rows;
++++++++end+if;
++++++++exception+when+others+then+recnum=0;
++++end;

++++return+recnum;

end;
$$
language+PLPGSQL;|code-block|syntax|javascript|1528863|entityMap^0|P|2|X|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>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 @&gt; 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) &lt;&gt; '') 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;
</code></pre>

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

<pre><code>DELETE FROM some_table CASCADE;
</code></pre>

The answers to <a href="https://stackoverflow.com/questions/45611/cascading-deletes-in-postgresql">this older question</a> make it seem like no such solution exists, but I figured I'd ask this question explicitly just to be sure.

CASCADE DELETE just once

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个Postgresql数据库，我想对其执行一些级联删除操作。但是，这些表不是使用ON DELETE CASCADE规则设置的。有没有什么方法可以让Postgresql只级联一次delete？等同于DELETE FROM some_table CASCADE;的答案使它看起来好像不存在这样的解决方案，但我想我会明确...

问仅级联删除一次
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅级联删除一次EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅级联删除一次
EN