--- 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

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?

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
effect.

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
done.

For what it's worth,
db