Modify UNIQUE in postgresql

Question

I have a table like so

        CREATE TABLE IF NOT EXISTS link_data (
            id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
            connection_id UUID NOT NULL,
            the_link TEXT NOT NULL,
            UNIQUE (connection_id, the_link),
            FOREIGN KEY (connection_id)
              REFERENCES connection(id)
              ON DELETE CASCADE
        );

I need to modify the table so it's only unique on connection_id, I.E. UNIQUE (connection_id) how do I alter the table to do it?

drop the existing constraint, create a new one

user330315
– user330315

2020-11-25 14:44:20 +00:00
Commented Nov 25, 2020 at 14:44 — user330315
– user330315, Commented Nov 25, 2020 at 14:44
How can I drop the constraint without the name of it?

Marc
– Marc

2020-11-25 14:46:00 +00:00
Commented Nov 25, 2020 at 14:46 — Marc
– Marc, Commented Nov 25, 2020 at 14:46
stackoverflow.com/questions/5273717/…

user330315
– user330315

2020-11-25 14:48:59 +00:00
Commented Nov 25, 2020 at 14:48 — user330315
– user330315, Commented Nov 25, 2020 at 14:48

Nikhil · Accepted Answer · 2020-11-25 14:58:56Z

1

First drop it by:

ALTER TABLE link_data 
DROP CONSTRAINT constraint_name;

then :

ALTER TABLE link_data 
ADD CONSTRAINT constraint_name UNIQUE (connection_id);

you can check table constraint by :

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = 'link_data ';

answered Nov 25, 2020 at 14:58

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

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

6 Comments

Marc Over a year ago

In my case I don't have direct access to the production DB. So I can't check the table constraint name. Or is the constraint name always the same so I can run it locally?

Bergi Over a year ago

@Marc Yes, if the CREATE TABLE/ADD CONSTRAINT statement didn't specify a name itself, the inferred default names are deterministic. (Not certain whether across versions, better check the documentation about that).

Nikhil Over a year ago

@Marc I guess the constraint name will be same.

Marc Over a year ago

So I tested ``` BEGIN; ALTER TABLE link_data DROP CONSTRAINT constraint_name; ALTER TABLE link_data ADD CONSTRAINT constraint_name UNIQUE (connection_id); COMMIT; ``` But it did not work, I got the error "could not create unique index "constraint_name"" Where the constraint name was fetched in a local build. I then tried using the same constraint name in our testing environment where this crash happened.

Nikhil Over a year ago

@Marc did you check constraint name by the last query?

|

Laurenz Albe · Accepted Answer · 2020-11-25 16:06:07Z

1

First find out the name of the unique constraint:

SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'link_data'
  AND constraint_type = 'UNIQUE';

or, using psql, simply type

\d link_data

Then drop the constraint with

ALTER TABLE link_data DROP CONSTRAINT whatever_name_you_found;

answered Nov 25, 2020 at 16:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Modify UNIQUE in postgresql

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related