How to drop constraint on postgers?

Question

I have created a constraint this way:

ALTER TABLE  varaus  ADD FOREIGN KEY ( varaus_id ) REFERENCES  kayttaja  ( id );

But when trying to drop it:

ALTER TABLE varaus DROP CONSTRAINT varaus_id;

I get the error:

ERROR:  constraint "varaus_id" of relation "varaus" does not exist

I have also tried:

ALTER TABLE varaus DROP CONSTRAINT varaus_fkey;
ALTER TABLE varaus DROP CONSTRAINT id;

varaus_id is the column name not the foreign key constraint name. — Ankit Bajpai, Jan 12 '20 at 10:12
https://stackoverflow.com/questions/12395466 or https://stackoverflow.com/questions/5273717 — , Jan 12 '20 at 10:38

score 2 · Answer 1 · answered Jan 12 '20 at 10:20

You have to find the name of constraint first using below query -

select constraint_name
from information_schema.table_constraints
where table_schema = 'your_schema_name'
and table_name='varaus'
and constraint_name like 'fk_%'

Then use those names in below query -

ALTER TABLE varaus DROP CONSTRAINT constraint_name

score 0 · Answer 2 · answered Jan 12 '20 at 18:02

Good example of why you should always name your constraints instead letting the DBMS generate a name. So instead of the initial creation of the FK use (for example)

alter table  varaus add  
      constraint var2kay_fk 
      foreign key ( varaus_id ) 
      references  kayttaja  ( id );

How to drop constraint on postgers?

2 Answers2