At 03:16 PM 10/06/2006, Adam wrote:

>This is a known issue.
>
>If you add a not null field to a table, you need to remember to set
>values to all pre-existing records in that field. The existing records
>are not checked when such a constraint is added.

This is true.

>The values are policed basically in before insert or update triggers.

This isn't true.

First, "before" triggers run before constraints are checked. If it
were not so, there would be no opportunity to validate NEW values in
"before" triggers.

Secondly, it gives the impression that you expect DEFAULT to work in
updates. It doesn't. It applies only to inserts and then, it
applies only where the constrained column is NOT in the INSERT list.

However, to say it is "policed" in updates is true. NULL in a
non-nullable column will cause an exception in either an insert or an update.

>Ideally, Firebird should either:
>
>a) reject the add field DDL if the table is not empty and includes a
>NOT NULL clause. This would have to allow you to add the field without
>the not null, and provide some method to add the constraint through
>DDL later.

I think (thought!) that this change had been done for Fb 2.0, i.e.
auto-filling of existing NULL values after . I just scanned the
release notes and can't find a report of it. If I'm right, that the
change was done, then it's my fault that it is missing from the release notes.

>OR
>
>b) provide for a default value to be used in pre-existing records

I can't work out what is meant by that.

>At the moment, such a mechanism is not available, so Firebird will let
>you shoot yourself in the foot. You have to do it in two transactions,
>one for the DDL then one to deal with the null records.
>
>ALTER TABLE FOO ADD BAR INTEGER NOT NULL;
>COMMIT;
>UPDATE FOO SET BAR = 0 WHERE BAR IS NULL;
>COMMIT;
>
>It is strongly advised to backup before performing ANY DDL operations,
>so the fact it uses two transactions is largely unimportant.

Actually, it *is* important to perform DDL and DML in separate
transactions. If you run the two commands in an isql script, or in
interactive isql, with AUTODDL set on, then isql takes care of
committing the DDL. Since you must always explicitly commit DML, you
still must commit after the DML runs. You can do so in the script or
by calling COMMIT after the script runs.

There's always a risk of getting into a messy situation if you are
using a third-party tool. or your own code, that doesn't implement
something equivalent to isql's AUTODDL. It doesn't mean you should
stop using those tools, just that you need to be aware of the need to
take care of separating DDL and DML operations explicitly.

Things can get messy where you have pending updates on the table or
dependencies in the cache at the time the ALTER operation is
done. The existing transactions won't know that the column exists
and, depending on conditions, will either except when the commit is
finally performed (remember, DEFAULT does not operate on updates),
cause an 'Object is in use' when the ALTER request is submitted, or
cause a conflict when the script tries to commit its DML.

./heLen