Add note about 'ALTER TABLE ADD <col_name> DEFAULT <some_not_null_value>' for existing rows

[pavel-zotov]

In https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-tbl-altradd
we can see caution about adding CHECK if a table is not empty:

When a new CHECK constraint is added, existing data is not tested for compliance

Something similar can be noted if we add column with DEFAULT clause and old data exist in the table.
Such column will contain default values (if they weren't explicitly specified) only for new rows.
Old records will have nulls for such column.

Script (note on: "add f02 int default 1"):

set bail ON;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

recreate table test(id int generated by default as identity, f01 int);
insert into test default values; ---------- id = 1
commit;

alter table test
    add f02 int default 1
   ,add constraint test_f02_chk check(f02 is not null)
;

insert into test(f01) values(1); ---------- id = 2
insert into test default values; ---------- id = 3
select * from test order by id;
commit;
show table test;

Output:

          ID          F01          F02
============ ============ ============
           1       <null>       <null>
           2            1            1
           3       <null>            1

Table: PUBLIC.TEST
ID                              INTEGER Not Null Identity (by default)
F01                             INTEGER Nullable
F02                             INTEGER Nullable default 1
CONSTRAINT TEST_F02_CHK:
  check(f02 is not null)

Maybe it's worth adding this example ?

[mrotteveel]

That is already implied by what a DEFAULT does (see Setting a DEFAULT Value), which is determine the value on insert if it is not explicitly specified. I see no need to add such a note. Notes and cautions are mostly for unexpected things (e.g. if specifying or changing a DEFAULT would actually change the value of existing columns).