Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL drop constraint with unknown name

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?

like image 234
takteek Avatar asked Sep 12 '12 20:09

takteek


People also ask

How do I drop an unnamed check constraint?

First, inspect the name given to the FK by the RDBMS, it has the same prefix and body but differs only in suffix hash. Second, select names of these constraints. Third, exec alter command that drops them.


1 Answers

To dynamically drop & recreate a foreign key constraint, you could wrap it all in a function or use the DO command:

DO
$body$
DECLARE
   _con text := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
      WHERE  conrelid = 'myschema.mytable'::regclass
      AND    confrelid = 'myschema.myreftable'::regclass
      LIMIT 1 -- there could be multiple fk constraints. Deal with it ...
      );

BEGIN
   EXECUTE '
      ALTER TABLE wuchtel12.bet DROP CONSTRAINT ' || _con;

   -- do stuff here

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

You must own the table to use ALTER TABLE.
Else you can create a function with LANGUAGE plpgsql SECURITY DEFINER (using the same body) and

ALTER FUNCTION foo() OWNER TO postgres;

postgres being a superuser here - or the owner of the table.
But be sure to know what the manual has to say about security.

The manual also has more on dynamic commands.

like image 156
Erwin Brandstetter Avatar answered Oct 11 '22 16:10

Erwin Brandstetter