I believe that when inserting a row into a table the CHECK constraints (which
includes any NOT NULL constraint) are checked at the wrong time, or at least
with the wrong data. The check should occur AFTER defaults and column affinity
is applied before the data record is stored, meaning that the constraints
should apply to the row as actually stored. Consider:
create table x (x integer default ('1') check (typeof(x) == 'integer'));
insert into x values (1);
insert into x values ('1');
insert into x default values;
The last two statements raise constraint errors, even though the values that
end up in the database would in fact pass the constraint:
create table x (x integer default ('1'));
insert into x values (1);
insert into x values ('1');
insert into x default values;
select x, typeof(x) from x;
1|integer
1|integer
1|integer
Similarly for the NOT NULL column constraint. If should apply to the data
actually stored, not the contents of the 'insert' the value may be modified
before it is actually inserted (the working of the PRIMARY KEY on a ROWID
table, or a default clause, for example) may modify the value before it is
stored.
Presently, the NOT NULL column constraint is to the input data, and not to the
row actually stored:
sqlite> create table x(x integer not null default (1));
sqlite> insert into x values (NULL);
Error: NOT NULL constraint failed: x.x
which also raises a NOT NULL constraint error even though the value stored will
not be NULL and therefore passes the constraint.
I do not know exactly where before triggers fire, but they should probably fire
directly on the input data after affinity is applied and before the check
contraints run. You could then use before triggers to limit or require
specifying NULL inputs on an insert even if those NULL values would be changed
to a default or computed value afterwards.
ie, the processing for inserting a record should be:
collect input data row
apply column affinity
fire before triggers
apply defaults, generated always, rowid etc.
apply column affinity to above columns
run constraints
store actul row
fire after triggers
I don't know if this would constitute a breaking change, but I don't think so
...
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
