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. Jun 2, 2015 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, 2015 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.. Jun 2, 2015 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. Jun 3, 2015 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. Jun 3, 2015 at 4:04

1 Answer 1


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, 2015 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, 2015 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, 2015 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. Jun 3, 2015 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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