PostgreSQL drop constraint with unknown name

Question

I have an SQL script that needs to drop several constraints and restore them at the end, but the constraint names are auto-generated and will be different each time the script is run.

I know how to get the constraint name from the table names, but it doesn't seem possible to use this information in the drop statement.

select conname from pg_constraint where
   conrelid = (select oid from pg_class where relname='table name')
   and confrelid = (select oid from pg_class where relname='reference table');

alter table something drop constraint (some subquery) is a syntax error.

Ideally I would like to get the constraint name and store it in a variable, but it doesn't seem that Postgres supports that and I can't make it work with psql \set.

Is this even possible?

You would at least need dynamic SQL to do this. Object names (tablenames,columnnames,etc) can Never be specified as variables (or subqueries) without first constructing the query (in a string) and then executing that. Using sed/awk to generate the "DROP xxx" lines in a file, and piping that file through psql could be a workaround. It will still be hard to maintain some "atomicity" (But DDL's are always difficult in that respect) — wildplasser
– wildplasser, Commented Sep 12, 2012 at 20:13
@wildplasser okay, I thought maybe psql would have some functionality to do that. Otherwise, you can just put your comment as answer. — takteek
– takteek, Commented Sep 12, 2012 at 20:41
I am not that handy with dynamic query building (I actually hate it) Others will probably fill it in. — wildplasser
– wildplasser, Commented Sep 12, 2012 at 20:49

Erwin Brandstetter · Accepted Answer · 2025-01-20 12:44:09Z

To dynamically drop & recreate a foreign key constraint, you could wrap it all in a FUNCTION, PROCEDURE (Postgres 11+), or a DO command:

DO
$do$
DECLARE
   /* There could be multiple FK constraints one table to another (even if uncomon).
    * This kind of assignment raises an exception if more than one rows are found:
    * "ERROR:  more than one row returned by a subquery used as an expression"
    * (As opposed to SELECT INTO ...)
    * If that's not what you want, resolve it.
    */
   _con text := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
      WHERE  conrelid = 'myschema.mytable'::regclass      -- source table
      AND    confrelid = 'myschema.myreftable'::regclass  -- FK target table
      );
BEGIN
   IF _con IS NULL THEN
      RAISE EXCEPTION 'FK constraint not found!';  -- flesh out msg ...
   END IF;

   EXECUTE
     'ALTER TABLE myschema.mytable DROP CONSTRAINT ' || _con;

   -- do stuff here

   EXECUTE
     'ALTER TABLE myschema.mytable
      ADD CONSTRAINT ' || _con || ' FOREIGN KEY (col)
      REFERENCES myschema.myreftable (col)';
END
$do$;

The executing role must own the table to use ALTER TABLE (or be a superuser).
Else you can create a SECURITY DEFINER function with using the same body, and:

ALTER FUNCTION foo() OWNER TO table_owner;

table_owner being the owner of the table (or a superuser). But read what the manual has to say about security.

The manual on dynamic commands.

jian · Accepted Answer · 2021-12-14 10:21:07Z

You can use stored procedure also.

CREATE OR REPLACE PROCEDURE public.p_costraint()
 LANGUAGE plpgsql
AS $procedure$
DECLARE _constrint text;
begin
-- for dynamic change the constraint. 
    _constrint := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
        WHERE  conrelid = 'test.contacts'::regclass
        AND    confrelid = 'test.customers'::regclass
      LIMIT 1 -- there could be multiple fk constraints. Deal with it ...
      );
    _constrint := _constrint || 'test';
    EXECUTE '
      ALTER TABLE test.contacts
      ADD CONSTRAINT ' || _constrint || ' FOREIGN KEY (customer_id)
      REFERENCES test.customers (customer_id)';
    RAISE NOTICE 'hello, world!';
end
$procedure$;

In here. constraint name is used as a text variable.
You can just call it: call public.p_costraint();
It will return :

NOTICE:  hello, world!
CALL

Collectives™ on Stack Overflow

PostgreSQL drop constraint with unknown name

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related