CODEWITHSUNDEEP
postgresql
johnlemon
johnlemon

Reputation: 21509

How to drop constraint by name in PostgreSQL?

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.

Upvotes: 120

Views: 178216

Answers (5)

Lekharaju Ennam
Lekharaju Ennam

Reputation: 1

To find constraints and Drop constraint for required tables :- in version 13 , tablename is schemaname.tablename

  1. 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_ddl

  2. get constraints details before dropping

    select  conrelid::regclass::text  tablename,  conname  constraintname,contype  
from  pg_constraint where connamespace::regnamespace::text = 'schemaname';

  3. 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.log

  4. add 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_ddl

  5. Validate constraint by running step 2

if not matching run step 4 again

Upvotes: 0

DevonDahon
DevonDahon

Reputation: 8370

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;

Upvotes: 2

Praveen Kumar C
Praveen Kumar C

Reputation: 449

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

Upvotes: 18

user330315
user330315

Reputation:

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.

Upvotes: 190

Kuberchaun
Kuberchaun

Reputation: 30362

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

Upvotes: 18

Related Questions