Is it safe to migrate from composite PK to single PK like this

user_13677923 0
Viewed 23

I have a table with a composite PK (code_id, user_id), that already have 100k records. Is it safe to move from composite PK to a single PK like in the example below?

ALTER TABLE orders
  DROP CONSTRAINT pk_code_id_user_id,
  ADD COLUMN order_id SERIAL,
  ADD CONSTRAINT pk_order_id PRIMARY KEY (order_id);
postgresql
1 Answers

Yes, that is safe and will work.

The only problem is that this statement can take a while to get done:

  • all rows have to get updated with the DEFAULT sequence value

  • a new unique index is built

  • PostgreSQL checks that order_id IS NOT NULL for all rows

During that time, the table is locked in ACCESS EXCLUSIVE mode, so it is not available even for concurrent reading.

But with a tiny table like that is should not be a big problem.

Things get more complicated if there are foreign key constraints that reference the old primary key constraint. You'd have to drop those as well and think of a good replacement.

user_64643080
Related