一般在POSTGRES中删除外键 [英] Drop foreign keys generally in POSTGRES

问题描述

通常如何删除外键。我的意思是，如果我在表中有许多外键约束。例如

How can I drop Foreign keys in general. I mean, if I have many foreign key constraints in a table. like

MonthlyEvaluatedBudgetTable约束：

MonthlyEvaluatedBudgetTable Contraints:

• budgetid_pk（主键）


• branchid_fk（外键）


• accountid_fk（外键）


• dept_fk（外键）

• budgetid_pk (Primary Key)
• branchid_fk (Foreign Key)
• accountid_fk (Foreign Key)
• dept_fk (Foreign Key)

postgres中是否有一种方法可以删除所有外键，而不是专门删除现有表中的所有外键？
Im使用此代码行将外键删除到现有表中。

Is there a way in postgres to drop all foreign keys in general and not specifically in an existing table? Im using this line of code to drop a foreign key in an existing table.

    ALTER TABLE "public"."monthlyevaluatedbudgettable"
    DROP CONSTRAINT "accountid_fk";

但是我想删除它而无需特别输入 accountid_fk ， branchid_fk ， dept_fk 。有办法吗？

But I want to drop It without specifically inputing accountid_fk,branchid_fk,dept_fk. Is there a way on it? thanks in advance.

推荐答案

在 DO 语句中将其圈起来，例如：

Loop it in DO statement, like:

b=# create table a (a int primary key, b int unique);
CREATE TABLE
b=# create table b (a int references a(a), b int references a(b));
CREATE TABLE
b=# do
$$
declare r record;
begin
for r in (select constraint_name from information_schema.table_constraints where table_schema = 'public' and table_name='b') loop
  raise info '%','dropping '||r.constraint_name;
  execute CONCAT('ALTER TABLE "public"."b" DROP CONSTRAINT '||r.constraint_name);
end loop;
end;
$$
;
INFO:  dropping b_a_fkey
INFO:  dropping b_b_fkey
DO

这篇关于一般在POSTGRES中删除外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！