|Re: [firebird-support] Re: Adding a field with NOT NULL constraint
Thanks for your answer.
Also note that when a not null field is created with a default value ( test4 ), not only any select returns its default value but also the engine considers it in compare statements as if it contained the default value.
This is also true if you decide to change the default value afterwards: compare statements will vary accordingly.
alter table table1 add test4 integer default 4 not null;
select distinct test4, iif(test4 = 4,1,0) as t4 from table1;
If you change the default value afterwards:
alter table t1 alter column test4 set default 5;
select distinct test4, iif(test4 = 5,1,0) as t4 from table1;
Extending Helen advice, whenever you add or change constraints related to the definition of a field using a DDL statement, you must update the value of that field in all records by means of a DML statement in order to ensure that no inconsistent data is saved or shown, whichever client library you use.
10.02.2018 22:33, Aldo Caruso wrote:
> A strange behavior is seen in the combination not null and no default
> value. It is returned as a 0 for selects but treated as a NULL when
In fact, the engine returns NULL. But query prepare describes the output
descriptor as NOT NULL. Some connectivity layers (including ISQL, IIRC)
get fooled, as NULL is not expected from a NOT NULL descriptor, and zero
/ empty string is returned. I recall that IBExpert is able to return
NULL in this case.