How can I drop Foreign keys
in general. I mean, if I have many foreign key constraints in a table. like
MonthlyEvaluatedBudgetTable Contraints:
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.
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
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
Just improving on @alexandar 's solution.
Use constraint_type = 'FOREIGN KEY'
instead of constraint_name like 'fk_%'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With