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.