Subject Re: [IBDI] Adding a NOT NULL column to a table
Author Dmitry Kuzmenko
Hello, Dmitry!

Dmitry Yemanov wrote:

> If a table is altered to add a column, values of this new column become NULL
> for all rows already existing in a table. Even if this new column has NOT
> NULL option, the result is still the same. And we have values that violate
> NOT NULL constraint. AFAIK, the only way to avoid this problem is to define
> DEFAULT option for the new column which will be used to populate its values.
> Does anyone know what the SQL standard says about it? Should data become
> inconsistent when DEFAULT option is not used?

You are returning to the question that was rejected about half of a year ago.

if engine will populate values, it will be horror for big tables - do
you want to wait ~1 hour while "alter table add field int not null" will be

Also there was a suggestion to disable such feature, i.e. to disable adding
new not null fields to non-empty tables.

That's why common decision was: this feature is dangerous is some situations,
when developer FORGOT to populate new not null column with correct data.
It is not always possible to fill not null with default data (in case of
FOREIGN KEY that does not have corresponding record for default value).
But, let this feature live, and let developers REMEMBER about all problems
that can be in case of INCOMPLETE work.

I mean, if you add NOT NULL column to new column you must understand what are
you doing and must remind yourself to FILL this column with appropriate data.

p.s. Also I've written some utility, that check for NULLS in NOT NULL columns.
I think check should be done with utility, or with backup.

Dmitry Kuzmenko, Epsylon Technologies.