"Dmitry Yemanov" wrote:

> "Ivan Prenosil" <Ivan.Prenosil@...> wrote:
> >
> > If you add *both* DEFAULT clause and NOT NULL constraint
> > you will get what you need.
>
> First, I wouldn't say this is exactly what he needs, because in fact records
> will still contain NULLs in this column. Perhaps nobody would notice the
> difference though.

I was just describing how it currently works (and correcting Helen who said
"Furthermore, you can add a column with a NOT NULL constraint on it
and immediately make your database unrestorable if you don't go in and
populate your existing rows.")

>
> Second, this hack may disappear in future versions (at least I always voted
> for that).

I agree. When adding new constraint, engine should always check whether
it is valid. I.e. with empty talbe
Alter Table tab Add NewCol Integer NOT NULL
should be o.k., but the same with non-empty table should fail
(although many peolpe will complain that it will break their ddl update scripts)
and correct procedure will be
Alter Table tab Add NewCol Integer;
Update tab Set NewCol=...;
Alter Table tab Alter NewCol Add constraint NOT NULL; << can't be done in current versions

Ivan