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.
Best 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:
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):