SQLite Forum

STRICT tables and UNIQUE indices
Hi All,

Just wanna add some of my personal experience to this thread, since it’s related to exactly this

Ive also run into a situation that requires uniqueness of nulls when in a unique index, and as far as I know, there is no good work around with sqlite.

I’m building a database product myself, where the tables/objects are created by the users themselves at a different level and sqlite tables get generated dynamically.

This means I can’t put in specific work arounds to the individual cases or use data type defaults (-1 or “”) Because these values might be meaningful themselves and won’t allow me to distinguish them from the “unset” concept.
Using magic values such as a very small negative number or some magic string will make looking at the raw data in the db misleading  so I don’t like that option either.  The other thing I don’t like about default values is it forces the developer to account for every different data type when looking for the unset,

algorithmic languages like c, c#, Java all allow the use of null to represent the unset concept in a way that is very useful.  You can check if something is null, not needing to worry about the type in any way.  In sql, u can check for nulls with “is null” logic in sql selects, but for nulls in the unique index there is no way to do what I need

One solution I’ve seen people suggest is to use partial indexes, leaving out the nullable
The problem with this is it won’t work if I have many nullable columns.  I would have to have an index for every combination of nulled column, N choose M combinations

Another solution is to use triggers or conflict clauses, which as far as I know will stop the automatic “insert or replace” statements from working on unique matches.  (I’ll be happy to be wrong on this one)

Sql is an incredibly old language and null represents a bunch of different concepts all muddled up together.  I think it would not be done the same way today

The algo languages have made null to mean more of unset rather than sql s “unknown” meaning.

This means bridging the algo and db worlds difficult because the sql world doesn’t do it cleanly and in particular sqlite can’t represent unique unset values in the index.
Not allowing the unique unset means interoperability, and data loss issues if pulling data from an sqlserver db or other null unique capable software

There is absolutely a valid use for both cases, and I think sqlite would benefit from having an extra keyword somewhere that allows null to be unique in indexes perhaps for each column or for the entire unique index.