how do you manage a database migration when you have to add a not null unique column to a table?

Viewed 38

Consider a table like this one (postgresql):

create table t1 (
  
  id serial not null primary key,
  code1 int not null,
  company_id int not null
);

create unique index idx1 on t1(code1, company_id);

I have to a new column code2 that will be not null and with a unique index like idx1, so the migration could be like:

alter table t1 add column code2 int not null;
create unique index idx2 on t1(code2, company_id);

The problem is: how do you manage the migration with the data that are already in the table? You cannot apply this migration neither I can define a default value for the column.

What I was thinking to do is:

  • create and execute migration that just add the column without any constraint
  • insert the data manually in the column not with a migration
  • create and execute another migration that adds the constraints to the column

Is this a good practice or there are other ways?

As migration tool I am using flyway but I think it's the same for every database migration tool.

0 Answers
Related