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

postgresql constraints ddl
Vinaya Nayak picture Vinaya Nayak · Jul 25, 2018 · Viewed 13.6k times · Source

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;

Answer

Kveld Ulf picture Kveld Ulf · Jul 25, 2018

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.

Related questions

Why can I create a table with PRIMARY KEY on a nullable column?

The following code creates a table without raising any errors: CREATE TABLE test( ID INTEGER NULL, CONSTRAINT PK_test PRIMARY KEY(ID) ) Note that I cannot insert a NULL, as expected: INSERT INTO test VALUES(1),(NULL) ERROR: null value in …

Read More
How to add "on delete cascade" constraints?

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter? # \d scores Table "public.scores" Column | Type | Modifiers ---------+-----------------------+----------- id | character varying(32) | gid | integer | money | …

Read More
PostgreSQL - disabling constraints

I have a table with approx 5 million rows which has a fk constraint referencing the primary key of another table (also approx 5 million rows). I need to delete about 75000 rows from both tables. I know that if I try doing …

Read More