Subject Re: [firebird-support] Adding a field with NOT NULL constraint
Author Helen Borrie
Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created
> field with its default value when there is a not null constraint ?

No. Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or
> insert of another concurrent transaction ?

There is no "behind the scenes filling". If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

update mytable set newfield = 1 where newfield is null

update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.


> ------------------------------------
> Posted by: Aldo Caruso <aldo.caruso@...>
> ------------------------------------

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> Visit and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu there.

> Also search the knowledgebases at

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------

> Yahoo Groups Links

Kind regards,
Helen Borrie