通常在POSTGRES中删除外键

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

我怎样才能删除外键。我的意思是，如果我在表中有许多外键约束。喜欢

MonthlyEvaluatedBudgetTable Contraints:

MonthlyEvaluatedBudgetTable Contraints：

budgetid_pk (Primary Key)
budgetid_pk（主键）
branchid_fk (Foreign Key)
branchid_fk（外键）
accountid_fk (Foreign Key)
accountid_fk（外键）
dept_fk (Foreign Key)
dept_fk（外键）

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.

在postgres中是否有一种方法可以放弃所有外键，而不是特定地放在现有表中？我使用这行代码删除现有表中的外键。

    ALTER TABLE "public"."monthlyevaluatedbudgettable"
    DROP CONSTRAINT "accountid_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.

但我想放弃它而不专门输入accountid_fk，branchid_fk，dept_fk。有没有办法呢？提前致谢。

2 个解决方案

#1

Loop it in DO statement, like:

在DO语句中循环它，如：

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

#2

Thank you Vao Tsun for the solution. It helped me.

感谢Vao Tsun的解决方案。它帮助了我。

In my case (Posgresql 9.6) I just had to add a minor "improvement"

在我的情况下（Posgresql 9.6）我只需添加一个小的“改进”

and constraint_name like 'fk_%' additional constraint to prevent errors like:

和constraint_name类似'fk_％'附加约束以防止错误，如：

PG::SyntaxError: ERROR: syntax error at or near "2200" LINE 1: ALTER TABLE "relationships" DROP CONSTRAINT 2200_856906_1_no...

PG :: SyntaxError：错误：语法错误在“2200”或附近第1行：ALTER TABLE“relationships”DROP CONSTRAINT 2200_856906_1_no ...

execute <<-SQL.squish
  DO $$
  declare r record;
  begin
    for r in (
      select constraint_name
      from information_schema.table_constraints
      where table_name='relationships'
      and constraint_name like 'fk_%'
    ) loop
    raise info '%','dropping '||r.constraint_name;
    execute CONCAT('ALTER TABLE "relationships" DROP CONSTRAINT '||r.constraint_name);
    end loop;
  end;
  $$
SQL

#1