On Friday, 31 January, 2020 14:39, Simon Slavin <slav...@bigfraud.org> wrote:
>On 31 Jan 2020, at 9:27pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> You are however correct that this is an "application consistency" >problem more than an SQLite problem and it is a lot of change for little >actual benefit. >How about this ? >A program (possibly part of the SQLite precompiled suite, possibly from a >third party) goes through any SQLite database as an integrity checker. >One of its jobs is to check that column constraints are not violated. >This cannot possible be wrong. A SQLite database with violated CHECK >clauses is, by definition, corrupt. pragma integrity_check and pragma quick_check already do this. >However, because the checks are performed on the values input, not the >values stored, some data in the database does violate a CHECK constraint. >Can this happen, given the behaviour Keith identified ? If so, I would >say that something is wrong. Yes. It is possible to "craft" a table and a check constraint such that the INSERT passes the constraint yet the integrity check fails: sqlite> create table x(x text not null check (typeof(x) == 'integer')); sqlite> insert into x values (1); sqlite> select x, typeof(x) from x; 1|text sqlite> pragma integrity_check; CHECK constraint failed in x That would elevate this to the status of a bug since it should be impossible to do this. -- 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