How to Drop Constraint in PostgreSQL: Syntax and Examples

To drop a constraint in PostgreSQL, use the ALTER TABLE command with DROP CONSTRAINT constraint_name. You must know the exact name of the constraint to remove it successfully.

Syntax

The basic syntax to drop a constraint in PostgreSQL is:

• ALTER TABLE table_name: specifies the table containing the constraint.
• DROP CONSTRAINT constraint_name: removes the named constraint from the table.

You must provide the exact constraint name, which can be found using system catalogs or “\d table_name” in psql.

sql

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

This example shows how to drop a unique constraint named users_email_key from the users table.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

-- Drop the unique constraint on email
ALTER TABLE users DROP CONSTRAINT users_email_key;

-- Verify the constraint is dropped
\d users

Output

                                  Table "public.users"
 Column |          Type          | Collation | Nullable |              Default              
--------+------------------------+-----------+----------+----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 email  | character varying(255) |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Common Pitfalls

Common mistakes when dropping constraints include:

• Trying to drop a constraint without knowing its exact name causes an error.
• Confusing constraint names with column names; they are often different.
• Attempting to drop system-generated constraints without checking dependencies.

Always check the constraint name first using \d table_name or querying pg_constraint.

sql

/* Wrong: dropping by column name (will fail) */
ALTER TABLE users DROP CONSTRAINT email;

/* Right: dropping by constraint name */
ALTER TABLE users DROP CONSTRAINT users_email_key;

Quick Reference

Command	Description
ALTER TABLE table_name DROP CONSTRAINT constraint_name;	Drops the specified constraint from the table.
\d table_name	Shows table details including constraint names in psql.
SELECT conname FROM pg_constraint WHERE conrelid = 'table_name'::regclass;	Query to list constraint names for a table.

Key Takeaways

Use ALTER TABLE with DROP CONSTRAINT and the exact constraint name to remove constraints.

Find constraint names using \d table_name or querying pg_constraint before dropping.

Constraint names are not always the same as column names.

Dropping constraints affects data integrity, so proceed carefully.

Always verify the constraint is removed by inspecting the table schema.