CODEWITHSUNDEEP
sqlpostgresql
Ali Moosavi
Ali Moosavi

Reputation: 179

How to turn a Unique Constraint into a Primary key in PostgreSQL?

I have a table that has a primary key on some columns, and another unique constraint on another column.

I dropped the old primary key, and now want to make the existing unique constraint the new primary key of the table, without dropping/rebuilding it from scratch.

The situation right now is like this:

Indexes:
    "t_review_routing_id_key" UNIQUE CONSTRAINT, btree (id)

When I run:

ALTER TABLE t_review_routing ADD PRIMARY KEY USING INDEX t_review_routing_id_key;

It says:

ERROR:  index "t_review_routing_id_key" is already associated with a constraint
LINE 1: ALTER TABLE t_review_routing ADD PRIMARY KEY USING INDEX t_r...

I also tried ALTER TABLE t_review_routing ADD CONSTRAINT t_review_routing_id_pkey PRIMARY KEY USING INDEX t_review_routing_id_key;, same error.

Any ideas?

Upvotes: 8

Views: 5372

Answers (1)

pozs
pozs

Reputation: 36244

You can drop your already existing constraint before creating the new one, but dropping the constraint will make the index disappear too.

But, you can create a new index CONCURRENTLY (example from the docs):

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

This method is explicitly mentioned at the docs of ALTER TABLE ... ADD table_constraint_using_index.

Upvotes: 6

Related Questions