Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-12 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Hello Helen,     One of the most dangerous consequences of forgetting to update values of the new added field, when it has constraints, is that, when recovering a database from a backup, errors are risen and the recover process aborts. Although backups are done raising no error, that doesn't

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-12 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Helen,     Thank you very much for you advices.     As a matter of fact, I had been changing table structures and stored procedures while other clients were connected since many years with no problems. Nevertheless, what you say is indeed true and can give rise potentially to trouble.    

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Helen Borrie hele...@iinet.net.au [firebird-support]
>   Your last advice concerns me a bit. Is it also valid for > changing stored procedures or triggers ? As an abiding principle - yes. But, for SPs and triggers, the effect varies according to a few factors. The BLR for these modules is cached on first use. Changes conducted whilst the m

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support]
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 a

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Thanks for your answer. I made some tests and found some strange exceptions to the rules you described. Consider the following DDL sentences, which create four fields with the for possible combinations for default value and not null constraint: alter table table1 add test1 integer; alter table

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Helen Borrie hele...@iinet.net.au [firebird-support]
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

Re: [firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
10.02.2018 16:32, Aldo Caruso aldo.car...@argencasas.com [firebird-support] wrote: > I discovered that when a field is added to a table with a NOT NULL > constraint and a default value, it is automatically filled with that > default value. No, it isn't. It would be too slow. Default value i

[firebird-support] Adding a field with NOT NULL constraint

2018-02-10 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Hello,     I discovered that when a field is added to a table with a NOT NULL constraint and a default value, it is automatically filled with that default value. Example: alter table table1 add field1 integer default 1 not null; commit; select distinct field1 from table1; FIELD1