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: )"
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
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
Ok, we’ll need to look into adapting dm-migrations appropriately. Thanks!
by Alex Coles
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:
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)
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.
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.