我有一个Postgresql数据库,我想在其上进行一些级联删除.但是,表未使用ON DELETE CASCADE规则进行设置.有什么方法可以执行删除并告诉Postgresql只将它级联一次吗？相当于的东西
DELETE FROM some_table CASCADE;
这个旧问题的答案似乎没有这样的解决方案存在,但我想我明确地问这个问题只是为了确定.
不能.只需编写一个想要级联的表的delete语句即可.
DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table); DELETE FROM some_table;
如果您真的想要 DELETE FROM some_table CASCADE; " 删除表中的所有行some_table ",则可以使用TRUNCATE而不是DELETE并且CASCADE始终支持.但是,如果要使用带有where子句的选择性删除,TRUNCATE则不够好.
USE WITH CARE - 这将删除所有具有外键约束的表的所有行以及some_table对这些表有约束的所有表等.
Postgres的支持CASCADE与TRUNCATE命令:
TRUNCATE some_table CASCADE;
这是事务性的(即可以回滚),尽管它与其他并发事务没有完全隔离,并且还有其他一些注意事项.阅读文档了解详细信息.
我写了一个(递归)函数来删除任何基于其主键的行.我写这个是因为我不想创建我的约束作为"删除级联".我希望能够删除复杂的数据集(作为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;
如果我理解正确,你应该能够通过删除外键约束,添加一个新的(将级联),执行你的东西,并重新创建限制外键约束来做你想要的.
例如:
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
当然,为了您的心理健康,您应该将这样的东西抽象到程序中.
我无法评论苍白的答案,所以我添加了自己的答案.Palehorse logick可以,但是大数据集的效率可能会很差.
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;
如果您在列上有索引并且数据集大于几个记录则会更快.
京公网安备 11010802040832号 | 京ICP备19059560号-6 