dm-oracle-adapter: auto_upgrade does not work on Oracle when adding a required property about dm-migrations HOT 5 OPEN

You should provide default value (DEFAULT ... clause in ALTER TABLE statement) when adding column with NOT NULL constraint for non-empty table .

by Raimonds Simanovskis

from dm-migrations.

Ok, we’ll need to look into adapting dm-migrations appropriately. Thanks!

by Alex Coles

from dm-migrations.

This actually seems like a valid constraint for dm-migrations to have across the board. You really can’t add a NOT NULL constraint to a non-empty table without a default, otherwise the database will be in an invalid state.

I would think we should raise an exception when a column with a NOT NULL constraint is added to a non-empty table with no default. We should instruct people to either provide the default value in the declaration, or have them do a three step process:

1. add the column, but allow NULL
2. populate every column so none of them are NULL
3. modify the column to add the NOT NULL constraint

I’m not that familiar with Oracle, but I’d hope that this process would work too (some googling says it will, but if @rsim could confirm I’d appreciate it).

Of course we want people to use the first approach (the one @rsim recommends above), but sometimes the default values have to be calculated from some other data, so the second approach would work for that.

by Dan Kubb (dkubb)

from dm-migrations.

Yes, this three step process will work on Oracle as well. If you add NOT NULL constraint on existing column then Oracle will go through all table rows and will validate if there are no NULL values in this column.

by Raimonds Simanovskis

from dm-migrations.

That seems reasonable. I would expect a good database to check all the existing data before adding a constraint. Whether it’s a NOT NULL constraint, a new foreign key or even a shorter length on a CHAR; that seems like the safest approach to take.

by Dan Kubb (dkubb)

from dm-migrations.