How to drop constraint by name in PostgreSQL?

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.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)