SQLite Forum

STRICT tables and UNIQUE indices
I'm not sure there is a misunderstanding, but in the interest of honest discourse, let me state it like this: The problem has two facets -

##1 - Is it sensible to have NULLs be NON-DISTINCT values in UNIQUE INDEXES?   
Certainly a couple fringe DB Engines feel this way, but most of them luckily do not. I'm not being flippant when I say "Luckily". There are Two considerations:   
- If you declare a column to be UNIQUE, you either

  1 -- absolutely never want to add two of the same things in there, or   
  2 -- sometimes want to add records which do not yet have their Unique Key assigned, so being able to insert a NULL helps. 

Now both 1 and 2 are reasonable and sensible ways of doing things, and works in the wider used interpretation of the standard.   
However, there is one little addition in that some people, while wanting to absolutely be able to add records without Keys to UNIQUE columns, now also want the added restriction of absolutely never being allowed to do that more than once. Why? This is less sensible, and I am not going to argue the case against it, you may have very valid reasons for doing so, but my point is that it should be clear this is not the general need, this is a very fringe benefit (and can already be achieved via the workarounds we agreed on).

##2 - Should it change?   
SQLite uses the interpretation of the SQL Standard that is most sensible and used by most other DB Engines (except a fringe few). It is not wrong, not buggy, not silly, not a design flaw, not a problem that needs fixing, not a stupid choice from long ago that makes developing difficult and should be eradicated... none of those.  What you are asking is to now, above and beyond its already best interpretation of the standard and accordance to other major engines, also expand its tool-set to allow a mode that is used by the lesser DB engines and lesser used application - in fact to widen its regime of interpretation. 

You can surely see the irony of how this is the very opposite of what STRICT mode tries to do.

To be clear - I'm not against the idea of allowing perhaps a Column parameter that will make NULLS non-distinct, perhaps a collation can achieve this? I just do not think that should be an effect of STRICT mode, ever.