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.
1
-
What version of PG are you on?Kuberchaun– Kuberchaun2011-03-11 14:12:26 +00:00Commented Mar 11, 2011 at 14:12
Add a comment
|
5 Answers
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.
Comments
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
Comments
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$$;
Comments
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;
Comments
To find constraints and Drop constraint for required tables :- in version 13 , tablename is schemaname.tablename
get DDL whole schema before drop constraints , casacade drop all ref constraints , that why need get all schema DDL
pg_dump -h $pg_host -U $pg_usr -d dbname --schema=schemaname -s -f dbname_schename_ddlget constraints details before dropping
select conrelid::regclass::text tablename, conname constraintname,contype from pg_constraint where connamespace::regnamespace::text = 'schemaname';Generate Drop constraint scripts
-- scriptsname mk_drp_tbl_cons.sql select ' alter table '||rtrim(conrelid::regclass::text)||' drop contraint '||rtrim(conname) ||' cascade ;' from pg_constraint where connamespace::regnamespace::text = 'tax' and conrelid::regclass::text in ('schemaname.table1','schemaname.tables2) ; psql -h $pg_host -U $pg_usr -d dbname -tAf -f mk_drp_tbl_cons.sql -o drp_tbl_cons.sql -- drop table constraints psql -h $pg_host -U $pg_usr -d dbname -tAf -f drp_tbl_cons.sql -o drp_tbl_cons.logadd drop constraints , by running extract DDL in step 1 ( run multiple times make constraint before after is matching
psql -h $pg_host -U $pg_usr -d dbname -dbname_schename_ddlValidate constraint by running step 2
if not matching run step 4 again
