Drop constraint by name in Postgresql

postgres drop constraint if exists
add unique constraint postgres
drop index postgres
postgres drop unique constraint without name
drop unique constraint postgres
postgres add constraint
postgres add foreign key constraint
drop foreign key constraint postgres

How can I drop a constraint name in Postgresql just by knowing the name? I have a list of constraints that are autogenerated 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.

Documentation: 9.1: Modifying Tables, New table constraint for the table. constraint_name. Name of an existing constraint to drop. CASCADE. Automatically drop objects that depend on the dropped  The drop constraint function allows the user to enter a constraint to drop from the table. The tool then generates the appropriate alter table drop constraint SQL command for dropping the constraint from the table. Listed below is an example of the SQL generated by the PostgreSQL Alter Table Drop Constraint function: ALTER TABLE sample.public.employee DROP CONSTRAINT test_const

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

Documentation: 9.1: ALTER TABLE, You need to retrieve the table names by running the following query: SELECT * FROM information_schema.constraint_table_usage WHERE  How to you drop a unique row constraint in PostgreSQL? If you need to make a change to a table's constraints (for example to drop an existing single column constraint in order to create a new compound column constraint) then use the following SQL: ALTER TABLE tablename DROP CONSTRAINT constraint_name

-- Drop the right foreign key constraint

ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;

NOTE:

affiliations -> Table Name

affiliations_organization_id_fkey ->Contraint name

Drop constraint by name in Postgresql, Information on how to drop a constraint from a PostgreSQL database table using the PostgreSQL Alter Table Drop Constraint command. If we want to delete a constraint, then we should remember the name of the constraints as it is easier for us to drop the constraints directly by its name. Otherwise, we will require to identify the system-generated name. In the psql, the following command can be used to find out the names.

PostgreSQL Drop Constraint from a PostgreSQL Database Table via , The syntax to drop a primary key in PostgreSQL is: ALTER TABLE table_name DROP CONSTRAINT constraint_name;. table_name: The name of the table to  The DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint. DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL:

PostgreSQL: Primary Keys, In this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables Because we didn't specify a name for the foreign key constraint explicitly, ALTER TABLE child_table DROP CONSTRAINT constraint_fkey;. 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.

PostgreSQL Foreign Key, You can't disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a  name. The name (optionally schema-qualified) of an existing table to alter. If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered.

Comments
  • What version of PG are you on?