Sean, Jorge,

> > I dont fully agree with you.
>
> Too bad the SQL committee doesn't agree with you.
>
> *You* are responsible for ensuring that existing data is updated to meet
> any new constraints that you add.

Although Jorge disagrees with me, I don't totally disagree with him. I
also agree with Sean that the buck stops with the person adding
additional constraints to make sure they haven't broken consistency.

The database server ideally should assist by preventing the developer
from shooting themselves in the foot wherever possible. Firebird does
this in many situations. Consider a table, and a stored procedure that
queries that table. If you attempt to drop the table, it will not let
you. Similarly, it will not let you disable a primary key index.

IMO, adding a new field with a not null constraint should work
something like this.

1. Only allow the addition of a not null field on an empty table,
otherwise raise an exception.

2. Create a command, something like alter mytable alter myfield not
null; (but let's not get too hung up on syntax). This command would
raise an exception if there are any records with a null in myfield.

When the developer wished to do as Jorge wishes to / has done, they
would simply.

1. Add the new field without the not null constraint.
2. Fill the field with whatever data is appropriate.
3. Alter the field to have the not null constraint.

And GBAK would never have to worry about this type of problem occuring.

(Being able to convert a field to have a not null constraint is also a
useful feature).

>
> > If the process can be improved, why dont do?
> > I've set my new field as "not null default = 0".

It is possible you do not want the pre-existing records to be set to
the default, but allowing the developer to specify what value to set
for pre-existing records is another possible approach, although less
powerful IMO.

>
> "Default" only applies to records which are INSERTed. Not to existing
> records.
>
>
> > I dont know how other DB engines resolves that problems, but I'm prety
> > sure that dont do what gbak does (At least SQL Server doesnt).
>

What does SQL Server do then?

NBackup in Firebird 2 does as you wish - copies the database as is
with consistency errors in tact.

> There is a gbak option which allows for restores without enforcing
> constraints. Have you tried this?

I think he has, his gripe is that why does it have to disable all
constraints, why can't it just be the one that is broken. A fair
enough question, not that upon discovering such an issue with one of
my databases would I ever leave it in a state where a restore would fail.

Jorge,

If you have an empty database (which you can get by using gbak -m)
then restoring it, this empty will contain all of your constraints.

Restore your problematic database without constraints, run whatever
DML to actually fix it and commit.

Download IBDatapump (or equivalent), and this can copy from your
original to your empty. Once finished, your 'empty' will now be
correctly filled.

Adam