How to drop a unique constraint on a column in Postgres?

Question

This is my database table

CREATE TABLE cart (
  id           UUID      NOT NULL PRIMARY KEY,
  shop_user_id UUID UNIQUE
);

And when I try to delete the UNIQUE constraint on shop_user_id I get the sql 42601 error

This is the query I use to delete the unique constraint

ALTER TABLE cart DROP UNIQUE shop_user_id;

Laurenz Albe · Accepted Answer · 2018-07-25 13:36:19Z

74

To find the name of the unique constraint, run

SELECT conname
FROM pg_constraint
WHERE conrelid = 'cart'::regclass
  AND contype = 'u';

Then drop the constraint as follows:

ALTER TABLE cart DROP CONSTRAINT cart_shop_user_id_key;

Replace cart_shop_user_id_key with whatever you got from the first query.

edited Jul 25, 2018 at 13:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Jul 25, 2018 at 13:26

Kveld Ulf

9278 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kveld Ulf Over a year ago

select * from information_schema.table_constraints; Find the constraint. Make sure to drop it from the table, then drop the unique value.

Vinaya Nayak Over a year ago

I ran select * from information_schema.table_constraints; This still shows the constraint name in there. But the problem is how to drop it?

niedomnie Over a year ago

Does not work always: select * from pg_indexes where indexname = 'train_on_track_history_unique'; returns index train_on_track_history_unique for table train_on_track_history, schema edr # alter table edr.train_on_track_history drop constraint train_on_track_history_unique; constraint "train_on_track_history_unique" of relation "train_on_track_history" does not exist #drop index train_on_track_history_unique; does not exist

KevsDe · Accepted Answer · 2021-03-28 10:27:12Z

5

For example:

    CREATE TABLE teachers( teacher_id SERIAL PRIMARY KEY,
email VARCHAR(250) UNIQUE NOT NULL);


select * from information_schema.table_constraints;

ALTER TABLE teachers DROP CONSTRAINT teachers_email_key;

answered Mar 28, 2021 at 10:27

KevsDe

511 silver badge1 bronze badge

1 Comment

ScrappyDev Over a year ago

I like this answer as an addendum to the accepted answer, since the table_constraints view provides a much more human readable result.

Collectives™ on Stack Overflow

How to drop a unique constraint on a column in Postgres?

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related