15

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

MonthlyEvaluatedBudgetTable Contraints:

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

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";

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

msagala25
  • 1,806
  • 2
  • 17
  • 24
  • 3
    What for? Anyways. It's simple. First you need to find all foreign keys ( http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys ), then you need to drop them. – pumbo Nov 23 '16 at 03:21

3 Answers3

20

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
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 1
    Please aware you are not dropping only foreign keys. This code will drop all primary key constraints, unique key constraint. If you want to filter foreign keys you can add constraint_type ='FOREIGN KEY' to your query. – Hasan Veli Soyalan Oct 19 '21 at 08:51
7

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

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

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

PG::SyntaxError: ERROR:  syntax error at or near "2200" LINE 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
Alexander
  • 71
  • 2
  • 5
4

Just improving on @alexandar 's solution.

Use constraint_type = 'FOREIGN KEY' instead of constraint_name like 'fk_%'

Laith Imad Qotom
  • 80
  • 4