Drop foreign keys generally in POSTGRES
Solution 1
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
Solution 2
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
msagala25
I graduated at Lyceum of the Philippines Laguna University. I write codes and develop system in banking industry.
Updated on May 30, 2020Comments
-
msagala25 almost 4 years
How can I drop
Foreign keys
in general. I mean, if I have many foreign key constraints in a table. likeMonthlyEvaluatedBudgetTable 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. -
Hasan Veli Soyalan over 2 yearsPlease 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.