How to Delete/Drop a Constraint In PostgresSQL

The statement is ALTER TABLE ... DROP CONSTRAINT ..., and it needs the constraint’s
real name, not the column’s:

ALTER TABLE authors DROP CONSTRAINT authors_email_key;
ALTER TABLE authors DROP CONSTRAINT IF EXISTS authors_email_key;   -- no error if already gone

Run it inside a transaction if you are unsure — DROP CONSTRAINT is fully
transactional and a ROLLBACK puts the constraint back.

Find the name first

Constraint names are only predictable when PostgreSQL generated them. \d tablename in
psql shows all of them:

\d authors

                Table "public.authors"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 id        | bigint  |           | not null |
 email     | text    |           |          |
 full_name | text    |           | not null |
 rating    | integer |           |          |
Indexes:
    "authors_pkey" PRIMARY KEY, btree (id)
    "authors_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "authors_rating_check" CHECK (rating >= 1 AND rating <= 5)
Referenced by:
    TABLE "books" CONSTRAINT "books_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(id)

Outside psql, query pg_constraint directly:

SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'authors'::regclass
ORDER BY contype;

       conname        | contype |                definition
----------------------+---------+-------------------------------------------
 authors_rating_check | c       | CHECK (((rating >= 1) AND (rating <= 5)))
 authors_pkey         | p       | PRIMARY KEY (id)
 authors_email_key    | u       | UNIQUE (email)
(3 rows)

contype is p primary key, f foreign key, u
unique, c check, x exclusion. Note the Referenced by line in the
psql output: that foreign key lives on books, not on authors, so it does not
appear in this query. It is about to matter.

The error everyone hits: something else depends on it

ALTER TABLE authors DROP CONSTRAINT authors_pkey;

ERROR:  cannot drop constraint authors_pkey on table authors because other objects depend on it
DETAIL:  constraint books_author_id_fkey on table books depends on index authors_pkey
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

A foreign key is implemented against the unique index behind the referenced table’s primary key, so
you cannot remove the primary key while anything references it. Follow the hint and PostgreSQL does
what it says — including the part you may not want:

ALTER TABLE authors DROP CONSTRAINT authors_pkey CASCADE;

NOTICE:  drop cascades to constraint books_author_id_fkey on table books
ALTER TABLE

SELECT conname FROM pg_constraint WHERE conrelid='books'::regclass;

  conname
------------
 books_pkey
(1 row)

books_author_id_fkey is gone. The referential integrity check on a table you did not
name in the statement has been deleted, and nothing but a NOTICE told you. If the
dependency is a foreign key you want to keep, the right move is to drop and recreate that foreign key
around the change rather than to reach for CASCADE.

Constraints and indexes are not the same object

A PRIMARY KEY or UNIQUE constraint owns an index. You cannot drop that
index on its own:

DROP INDEX authors_email_key;

ERROR:  cannot drop index authors_email_key because constraint authors_email_key on table authors requires it
HINT:  You can drop constraint authors_email_key on table authors instead.

Dropping the constraint takes the index with it. Before: authors_pkey,
authors_email_key. After ALTER TABLE authors DROP CONSTRAINT
authors_email_key;, pg_indexes lists only authors_pkey.

The reverse case is the one that produces a confusing error. An index created with
CREATE UNIQUE INDEX — rather than as a constraint — enforces uniqueness but is not a
constraint, and has no row in pg_constraint:

CREATE UNIQUE INDEX books_isbn_uniq ON books(isbn);

SELECT count(*) FROM pg_constraint WHERE conname='books_isbn_uniq';   -- 0

ALTER TABLE books DROP CONSTRAINT books_isbn_uniq;
ERROR:  constraint "books_isbn_uniq" of relation "books" does not exist

DROP INDEX books_isbn_uniq;
DROP INDEX

If DROP CONSTRAINT insists a constraint does not exist while \d clearly
shows something enforcing uniqueness, check whether \d labelled it
UNIQUE CONSTRAINT or just UNIQUE. The second one needs
DROP INDEX.

NOT NULL is not a constraint you can drop by name

An earlier version of this page listed NOT NULL beside the others. On PostgreSQL 17 there is no
pg_constraint row to name:

SELECT conname FROM pg_constraint
WHERE conrelid='authors'::regclass AND conname LIKE '%full_name%';
(0 rows)

ALTER TABLE authors DROP CONSTRAINT authors_full_name_not_null;
ERROR:  constraint "authors_full_name_not_null" of relation "authors" does not exist

NOT NULL is a column attribute (pg_attribute.attnotnull), so it is removed through the
column:

ALTER TABLE authors ALTER COLUMN full_name DROP NOT NULL;

SELECT attname, attnotnull FROM pg_attribute
WHERE attrelid='authors'::regclass AND attname='full_name';

  attname  | attnotnull
-----------+------------
 full_name | f

A CHECK (col IS NOT NULL) written by hand is a real constraint with a name,
and that one does come off with DROP CONSTRAINT. The two look identical in application
code and are removed completely differently.

The lock is the production risk

BEGIN;
ALTER TABLE authors DROP CONSTRAINT authors_rating_check;
SELECT mode FROM pg_locks WHERE relation='authors'::regclass AND pid=pg_backend_pid();

        mode
---------------------
 AccessExclusiveLock
(1 row)

The catalog change itself is instant — no table rewrite, no data validation — but
ACCESS EXCLUSIVE blocks every read and write on the table until the transaction commits.
On a busy table the danger is not the ALTER; it is that the ALTER waits
behind one long-running query and every query arriving after it queues behind the ALTER.
Set lock_timeout before running it (SET lock_timeout = '3s';) so the statement
gives up instead of building a queue.

Reproduce this

The script at /verify/how-to-drop-a-constraint.sh creates its own throwaway
PostgreSQL cluster on a spare port, builds these two tables, and asserts every error message and
result above, including the exact ERROR and DETAIL text. It prints PASS or
FAIL per claim and deletes the cluster on exit.