Drop primary key without dropping an index

Question

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to another column pair. I want to avoid dropping and recreating the index. Can just the PRIMARY KEY 'attribute' on the index be dropped? Dropping the constraint removes the index.

I am using PostgreSQL version 9.1.

score 7 · Accepted Answer · answered Jun 03 '15 at 04:23

I don't think there is a supported way to do this. Consider the comments so far.

If you are concerned about effects on concurrent transactions, there is a code example in the manual:

To recreate a primary key constraint, without blocking updates while the index is rebuilt:

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;

For your case, you could create the same index the PK uses a second time and the index used by your new PK. Then drop the PK like in the example, add a different (unlike the example) PK based on the new index and a new UNIQUE constraint based on the duplicated former PK index. All in a single statement. That would only lock the table for a millisecond.
Three separate commands (not in one transaction):

CREATE UNIQUE INDEX CONCURRENTLY dupe_of_old_pk_idx ON tbl (old_pk_id);
CREATE UNIQUE INDEX CONCURRENTLY new_pk_idx ON tbl (new_pk_id1, new_pk_id2);

ALTER TABLE tbl
   DROP CONSTRAINT tbl_pkey
 , ADD  CONSTRAINT tbl_uni  UNIQUE USING INDEX dupe_of_old_pk_idx
 , ADD  CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX new_pk_temp_idx;

Drop primary key without dropping an index

1 Answers1

Linked