Subject Re: [firebird-support] Adding a field with NOT NULL constraint
Author Aldo Caruso

Hello Helen,

  I saw your answer after I had replied to Dimitry. It is clear that there is no automatic filling and I'm responsible to fill fields with not null constraint.

  Your last advice concerns me a bit. Is it also valid for changing stored procedures or triggers ?

  Should I have always to disconnect every client before executing DDL sentences ?

Thanks

Aldo


El 10/02/18 a las 15:41, Helen Borrie helebor@... [firebird-support] escribió:
 

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.

HB

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

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

> Visit http://www.firebirdsql.org 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
> http://www.ibphoenix.com/resources/documents/

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

> Yahoo Groups Links

--
Kind regards,
Helen Borrie