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;

Unutterable

Why are you checking the Oracle documentation (and tagging this question with 'plsql') when you're using PostgreSQL? What's the exact error (which keyword isn't recognized)? – Ricebird

ERROR: syntax error at or near "MODIFY" LINE 1: Alter table public.public_insurer_credit MODIFY CONSTRAINT p... ^ (0.359 sec) – Unutterable

I was a noob and checking the documentation that was provided to me. Never occurred to me that the DBA would give me the wrong documentation. Comment was deserved. – Unutterable

@Unutterable everybody was once a noob, the only shame is forgetting that. At least you mentioned they were Oracle docs, so it was easy to figure out. The comment was not deserved. – Trite

Answer 1

According to the correct manual (which is supplied by PostgreSQL, not by Oracle), there is no modify constraint available in the ALTER TABLE statement:

Here is the link to the correct manual:

http://www.postgresql.org/docs/current/static/sql-altertable.html

Deepsea

Thank you for the link to the proper documentation. I was supplied with the Oracle PL/SQL documentation from our DBA. Figures. – Unutterable

Oracle and Oracle's PL/SQL are completely different things. The only possible reason they did that is because you are using EnterpriseDB's Advanced Server which has support for Oracle's PL/SQL (and other Oracle compatibility features). But then they should have given you the EnterpriseDB manual, not the Oracle manual – Deepsea

Best guess at downvote, the answer simply links to an external site without providing future readers an explanation of how a constraint can be changed correctly. – Colliery

Answer 2

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;

Underclothes

Beware: if I understand correctly, DDL statements take an AccessExclusive lock on the table, so if these commands take a long time, your site will grind to a halt until the commands complete. The documentation page has more details, including how to specify an index explicitly rather than having one generated automatically. – Abbess

A single transaction is good. A single command is even better. Example: https://mcmap.net/q/204937/-how-to-alter-quot-references-quot-in-postgresql – Tomi

As of 9.4 you can indeed alter foreign key constraints in Postgres: postgresql.org/docs/current/sql-altertable.html @mkurz answer below should be accepted as the new correct answer. – Mediation

Answer 3

As of version 9.4, PostgreSQL supports ALTER TABLE ... ALTER CONSTRAINT for foreign keys.

This features will "Allow constraint attributes to be altered, so the default setting of NOT DEFERRABLE can be altered to DEFERRABLE and back." Looking at your question I think that is (kind of) what you have been looking for.

More detailed information and an example can be found here:
http://www.depesz.com/2013/06/30/waiting-for-9-4-alter-table-alter-constraint-for-fks/

Ascospore

Note that the 9.4 docs say Currently only foreign key constraints may be altered.! – Pilcher

We are at 12+, and still no way to to alter non-foreign constraints. Need to add DEFERRABLE to some, and can't do it without dropping/recreating them. – Sophist

Currently only foreign key constraints may be altered. is still true in Postgres 16, fwiw. – Unwell

Answer 4

ALTER CONSTRAINT would require knowing of foreign key name, which is not always convenient.

Here is function, where you need to know only table and column names. Usage:

select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');

Function:

CREATE OR REPLACE FUNCTION 
    replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR) 
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN

SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' 
   AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;

EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name || 
', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' ||
' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';';

RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column ||
 ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;

END;
$$ LANGUAGE plpgsql;

Be aware: this function won't copy attributes of initial foreign key. It only takes foreign table name / column name, drops current key and replaces with new one.

Mcginnis

Very interesting solution – Lidialidice