Drop constraint by name in Postgresql

Question

How can I drop a constraint in PostgreSQL just by knowing the name?

I have a list of constraints that are auto-generated by a 3rd party script. I need to delete them without knowing the table name just the constraint name.

Answer 1

You need to retrieve the table names by running the following query:

SELECT * FROM information_schema.constraint_table_usage WHERE table_name = 'your_table' 

Alternatively you can use pg_constraint to retrieve this information

select n.nspname as schema_name,        t.relname as table_name,        c.conname as constraint_name from pg_constraint c   join pg_class t on c.conrelid = t.oid   join pg_namespace n on t.relnamespace = n.oid where t.relname = 'your_table_name'; 

Then you can run the required ALTER TABLE statement:

ALTER TABLE your_table DROP CONSTRAINT constraint_name; 

Of course you can make the query return the complete alter statement:

SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';' FROM information_schema.constraint_table_usage WHERE table_name in ('your_table', 'other_table') 

Don't forget to include the table_schema in the WHERE clause (and the ALTER statement) if there are multiple schemas with the same tables.

Answer 2

If your on 9.x of PG you could make use of the DO statement to run this. Just do what a_horse_with_no_name did, but apply it to a DO statement.

DO $$DECLARE r record;     BEGIN         FOR r IN SELECT table_name,constraint_name                  FROM information_schema.constraint_table_usage                  WHERE table_name IN ('your_table', 'other_table')         LOOP             EXECUTE 'ALTER TABLE ' || quote_ident(r.table_name)|| ' DROP CONSTRAINT '|| quote_ident(r.constraint_name) || ';';         END LOOP;     END$$;