dm-oracle-adapter: auto_upgrade does not work on Oracle when adding a required property

[solnic]

To reproduce:

1. Oracle 10g, installed per instructions here: http://blog.rayapps.com/2009/09/14/how-to-install-oracle-database-10g-on-mac-os-x-snow-leopard/
2. Define a model.
3. @automigrate@ the database.
4. Add a property that is required.
5. @autoupgrade@ the database.

Result:

  ERROR - [25/Mar/2011 18:14:56] "ORA-01758: table must be empty to add mandatory (NOT NULL) column
 (code: 1758, sql state: 42000, query: ALTER TABLE "LOCATIONS" ADD "TITLE" VARCHAR2(100) NOT NULL, uri: )"
rake aborted!
ORA-01758: table must be empty to add mandatory (NOT NULL) column

@Raimonds I believe this is an inherent limitation of Oracle?

---

Created by Alex Coles - 2011-03-25 17:13:49 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1499

[solnic]

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

[solnic]

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

by Alex Coles

[solnic]

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)

[solnic]

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

[solnic]

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)