Subject Re: Adding a NOT NULL column to a table
--- In IBDI@y..., "Dmitry Yemanov" <dimitr_ex@y...> wrote:
> Hi,
> 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
> NOT NULL constraint. AFAIK, the only way to avoid this problem is to
> DEFAULT option for the new column which will be used to populate its
> Does anyone know what the SQL standard says about it? Should data
> inconsistent when DEFAULT option is not used?

The SQL standard says that when you add the new column, the value of
that column in each row is set to the default value for the column.

As someone quoted in another message, the SQL92 rules say:
"1) The column defined by the <column definition> is added to T.
2) Let C be the column added to T. Every value in C is the default
value for C."

If there is no explicit default value specified (for the column or the
domain), then the default value is the null value.
If that would cause a constraint (such as NOT NULL) to be violated,
then an exception should occur, and the alter statement should have no

In the SQL92 standard, the evaluation of constraints can be deferred,
which would allow you to alter the table to add the column (violating
the constraint, if it were checked at that point), then assign values
to the columns as a separate step, then have the constraint checking

For what it's worth,