Trying to modify a constraint in PostgreSQL

Question

I have checked the documentation provided by Oracle and found a way to modify a constraint without dropping the table. Problem is, it errors out at modify as it does not recognize the keyword.

Using EMS SQL Manager for PostgreSQL.

Alter table public.public_insurer_credit MODIFY CONSTRAINT public_insurer_credit_fk1     deferrable, initially deferred; 

I was able to work around it by dropping the constraint using :

ALTER TABLE "public"."public_insurer_credit"   DROP CONSTRAINT "public_insurer_credit_fk1" RESTRICT;  ALTER TABLE "public"."public_insurer_credit"   ADD CONSTRAINT "public_insurer_credit_fk1" FOREIGN KEY ("branch_id", "order_id", "public_insurer_id")     REFERENCES "public"."order_public_insurer"("branch_id", "order_id", "public_insurer_id")     ON UPDATE CASCADE     ON DELETE NO ACTION     DEFERRABLE      INITIALLY DEFERRED; 

Answer 1

There is no ALTER command for constraints in Postgres. The easiest way to accomplish this is to drop the constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.

BEGIN; ALTER TABLE t1 DROP CONSTRAINT ... ALTER TABLE t1 ADD CONSTRAINT ... COMMIT;