Trying to modify a constraint in PostgreSQL

sql postgresql alter
MISMajorDeveloperAnyways picture MISMajorDeveloperAnyways · Nov 30, 2011 · Viewed 77.7k times · Source

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

Chris Cashwell picture Chris Cashwell · Nov 30, 2011

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;

Related questions

Postgresql - Using subqueries with alter sequence expressions

Is it possible to use subqueries within alter expressions in PostgreSQL? I want to alter a sequence value based on a primary key column value. I tried using the following expression, but it wouldn't execute. alter sequence public.sequenceX restart …

Read More
alter column from time with time zone to timestamp

I am having trouble changing a column called end_date in a table called key_request from time with time zone to timestamp in my Postgres database . I have tried using the following code: alter table key_request alter column …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More