How to drop constraint by name in PostgreSQL?

How can I drop a constraint in PostgreSQL just by kno开发者_JS百科wing 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.

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.

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$$;

Drop the right foreign key constraint

ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;

NOTE:

affiliations -> Table Name

affiliations_organization_id_fkey ->Constraint name

List contraints:

SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class     rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = 'your_schema' AND
      rel.relname = 'your_table';

Remove the contraint:

ALTER TABLE your_schema.your_table DROP CONSTRAINT the_constraint_name;