CODEWITHSUNDEEP
postgresql
Marc
Marc

Reputation: 704

Modify UNIQUE in postgresql

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?

Upvotes: 2

Views: 80

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246308

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;

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

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 ';

Upvotes: 1

Related Questions