CODEWITHSUNDEEP
postgresqlconstraintsddl
Vinaya Nayak
Vinaya Nayak

Reputation: 1291

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

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;

Upvotes: 38

Views: 55353

Answers (2)

KevsDe
KevsDe

Reputation: 41

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;

Upvotes: 4

Kveld Ulf
Kveld Ulf

Reputation: 887

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.

Upvotes: 70

Related Questions