SQLite Forum

STRICT tables and UNIQUE indices
I think we misunderstand each other. I was referencing https://sqlite.org/faq.html#q26.

So null is distinct for many but not in all databases. I am asking for a non distinct null value in unique indices as 'non value'.

I know how to handle the -1 case but this is not helping if -1 is a valid value. I try to give an example. You can have an value which is an int64 and you use it as an key. Lets assume that all integers can be a valid value and there can be the case of a non value. This could be mapped to null but can't because 'non value' is not distinct in most cases. It is only a special case. So you cannot use null for that case which comes up very often in many pattern. I know ways to work around it. I thought that would be clear.

Why I was coming up with it in the strict table case? There you can set a column to integer and it would be not anymore possible to workaround it with an other type like an empty bytearray. You have to add an extra column.