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.

  • 2
    I think there is no way to do this. The constraint "owns" the index. When it is removed, the index goes as well. – ypercubeᵀᴹ Jun 2 '15 at 16:53
  • 2
    You can promote an index to primary key with alter table NameOfTable add constraint NameOfPrimaryKeyConstraint primary key using NameOfExistingIndex, but I don't think the reverse is possible. – Andomar Jun 2 '15 at 17:38
  • 1
    If you are worried about the time it takes to create the index after dropping it and the potential vulnerability to your data during that window - you could always create the new index BEFORE you drop the constraint.. – Joishi Bodio Jun 2 '15 at 22:26
  • 1
    You might be able to do it by hacking pg_depend to break the dependency between the index and constraint, but that might just cause errors in the DROP CONSTRAINT. I don't think there's a supported user-level way to do this. – Craig Ringer Jun 3 '15 at 0:54
  • 1
    I ran a quick test converting a simple PK to a UNIQUE constraint after identifying the OID for the pg_constraint entry. I also set pg_class.relhaspkey to false. Like UPDATE pg_constraint SET contype = 'u' WHERE oid = 23456; UPDATE pg_class SET relhaspkey = false WHERE oid = 'contents'::regclass; But that still didn't do it. Still could not create a new PK: ERROR: multiple primary keys for table "contents" are not allowed. So there is more .. I stopped there. I'd rather not try and mess with the system catalog for a relevant table. – Erwin Brandstetter Jun 3 '15 at 4:04

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

 , ADD  CONSTRAINT tbl_uni  UNIQUE USING INDEX dupe_of_old_pk_idx
 , ADD  CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX new_pk_temp_idx;
  • Thank you. I was aware of this solution and you are right. I just wanted to avoid the I/O on production server while creating the duplicate index, which is not possible (or unsupported/undocumented) right now as stated in your answer and comments. – xchg Jun 3 '15 at 6:51
  • Looking at your answer more closely, is there any reason the commands can't be run in one transaction? – xchg Jun 3 '15 at 6:54
  • 1
    @xchg You cannot run CREATE INDEX CONCURRENTLY in a transaction - it would defeat the idea behind using CONCURRENTLY, namely avoid blocking. – dezso Jun 3 '15 at 7:50
  • @xchg: What dezso said. Plus, it wouldn't buy you anything to run the CREATE INDEX statements inside a transaction - except keeping more locks for an extended period of time - not what you would want. – Erwin Brandstetter Jun 3 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.