> > I wish I hadn't. It's an open question as to whether which is worse --
> > the code or the idea.
>
> Before this change (whenever it's been done), after ALTER TABLE ADD <col>
> <type> NOT NULL you got two effects:
>
> 1) Your NOT NULL column returns NULLs for all existing rows (clearly
> violating the SQL rules)
> 2) The backup is totally unrestorable
>
> I think Borland has made an attempt to at least partially fix the both
> problems. A very bad attempt, I should say.
>
> In FB2, the second issue can be worked around using the -no_validity

restore

> switch (previously it didn't take NOT NULL constraints into account). But
> the former issue is unavoidable since the very beginning.
>
> I always insisted that such DDL shouldn't be allowed at all. Only this
> solution would cure everything. If we expect users to put some value in

the

> new column, then let them add it as nullable only and only after

populating

> it with values they could add a NOT NULL constraint. The same final effect
> with 100% of reliability.

I don't agree here. There are other possibilities as well, some that are
easier for the user.

1) allow adding a not null column, but with a default. Evaluate the default
for each NULL value in the table.

2) allow adding a not null column, check for NULLs and raise an error if
NULLs are found but no DEFAULT has been given.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com