SQLite Forum

STRICT tables and UNIQUE indices
As soon as one allows nullable columns in a DB schema, one has to accept their positive and negative consequences. The semantics of "null" is manyfold, its most common interpretations being "unknown", "missing", "not applicable", and the like. 
In a strict application of the historical relational model, "null"s are not allowed in base tables. Indeed, they can cause many logic problems that translate into subtile errors in SQL queries that may be tricky to detect and fix. Actually, "null" is not a value but a tag or a marker denoting the absence of value. 
One way to get rid of "null"'s in base tables is to decompose them so that each nullable column is isolated in its own table. 
The problem is that SQL has an ambiguous attitude towards "null"'s. Sometimes "null"'s are treated as tags (the evaluation of ["null" = "null"] is not "True" but "Unknown") while elsewhere they are considered as ordinary values, as in these two cases:

1. "create table T(A,B);"
   "select B,count(*) from T group by B;"
   the resultset of which may include a row such as "(null,37)", suggesting that "null"'s
   are comparable.

2. in some RDBMS's (as far as I remember, SQL Server, MS Access for example) a unique
   index based on nullable column "A" allows only ONE base row "where A is null", also
   suggesting that "null"'s are comparable. 
   This interpretation makes this index practically useless and is generally considered
   as a design bug. 
   The fix recommended is to create a partial unique index on the rows "where A is not null".

Fortunately, the interpretation of "unique indexes on nullable columns" as implemented in SQLite complies with the standard. IMHO, the idea of a STRICT mode affecting the semantics of unique index will only add confusion for little added value.