--- In firebird-support@yahoogroups.com, "dr_bentonquest"
<bentonquest@...> wrote:

>
> Hola Toño,
>
> Sorry, I should have mentioned before that I added those 'not null'
> fields to the table *after* the table was created. I used an ALTER
> statement to add the fields. Do you think this affects the constraint
> enforcement?
>
> I have searched my application code and the database metadata looking
> for 'insert into detallenom' and the procedure I quoted before is the
> only place where new records are inserted into the table.
>
> Like you, I am also puzzled to find 'null' values on those fields. I
> understand that the problem might not be reproducible on your side,
> but I could send you (or anyone) the gdb file so you can see the null
> values in there. For me it's an eye-popping situation. I feel like
> betrayed by Firebird :)
>
> BTW, I could send you the gdb file but *not* the gbk file, as you
> cannot restore it because the restore process tries to enforce the
> 'not null' constraint but it can't due to the null values, so the
> restore is aborted.
>
> Ideas, anyone?
>
> Regards,
>
> -Benton

Hello Benton,

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. The values are
policed basically in before insert or update triggers.

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.

OR

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

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.

By the way, you can probably restore if you use the -i switch

Secondly, once you have done this, rename your database to use a fdb
extension to work around some 'features' in windows system restore.

Adam