Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Here is the result of current (for who does not know how it works and
are lazy to test) and proposed new way. I'm not going to explain what's
wrong again, and anyway it's obvious reading that.


current
==============================================
create table t (
  n1 integer
);

insert into t (n1) values (1);
commit;

alter table t add n2 integer default 2 not null;

select * from t;

          N1           N2
============ ============
           1            2


insert into t (n1) values (2);

select * from t;

          N1           N2
============ ============
           1            2
           2            2


commit;

alter table t add t1 time default current_time not null;

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 00:00:00.0000
           2            2 00:00:00.0000


-- wait
insert into t (n1) values (3);
-- wait
insert into t (n1) values (4);

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 00:00:00.0000
           2            2 00:00:00.0000
           3            2 18:19:17.0000
           4            2 18:19:20.0000


alter table t
  alter n2 null,
  alter t1 null;

select * from t;

          N1           N2            T1
============ ============ =============
           1       <null>        <null>
           2            2        <null>
           3            2 18:19:17.0000
           4            2 18:19:20.0000


commit;

alter table t add t2 time not null;

select * from t;

          N1           N2            T1            T2
============ ============ ============= =============
           1       <null>        <null> 00:00:00.0000
           2            2        <null> 00:00:00.0000
           3            2 18:19:17.0000 00:00:00.0000
           4            2 18:19:20.0000 00:00:00.0000


delete from t;
commit;

alter table t add t3 time not null;

insert into t (n1) values (5);
Statement failed, SQLSTATE = 23000
validation error for column T2, value "*** null ***"

commit;

drop table t;
==============================================


new
==============================================
create table t (
  n1 integer
);

insert into t (n1) values (1);
commit;

alter table t add n2 integer default 2 not null;

select * from t;

          N1           N2
============ ============
           1            2


insert into t (n1) values (2);

select * from t;

          N1           N2
============ ============
           1            2
           2            2


commit;

alter table t add t1 time default current_time not null;

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 18:15:11.0000
           2            2 18:15:11.0000


-- wait
insert into t (n1) values (3);
-- wait
insert into t (n1) values (4);

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 18:15:11.0000
           2            2 18:15:11.0000
           3            2 18:15:13.0000
           4            2 18:15:17.0000


alter table t
  alter n2 null,
  alter t1 null;

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 18:15:11.0000
           2            2 18:15:11.0000
           3            2 18:15:13.0000
           4            2 18:15:17.0000


commit;

alter table t add t2 time not null;
Statement failed, SQLSTATE = 22006
unsuccessful metadata update
-Cannot make field T2 NOT NULL because there are NULLs present

select * from t;

          N1           N2            T1
============ ============ =============
           1            2 18:15:11.0000
           2            2 18:15:11.0000
           3            2 18:15:13.0000
           4            2 18:15:17.0000


delete from t;
commit;

alter table t add t3 time not null;

insert into t (n1) values (5);
Statement failed, SQLSTATE = 23000
validation error for column T3, value "*** null ***"

commit;

drop table t;
==============================================


Adriano