How to drop constraint by name in PostgreSQL?
PostgresqlPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - 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.
Solution 2 - Postgresql
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$$;
Solution 3 - Postgresql
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