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.
Answer :
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;