SQLite Forum

STRICT tables and UNIQUE indices
I'm sorry to say, but this entire thesis seems biased towards imperfect expectation born from limited experience. Not being nasty - I have had this happen to me before (about other concepts) so I recognize this reasoning from my own imperfect past.

Let me deal with the points raised one by one:

> ... and as far as I know, there is no good work around with sqlite.

Several workarounds (good ones) have been demonstrated in this thread, even some by the very poster who originally posed the question.

> algorithmic languages like c, c#, Java all allow the use of null to represent the unset concept in a way that is very useful.

So does SQL. (More below)

> You can check if something is null, not needing to worry about the type in any way.

Yeah, same as in SQL. You even go right ahead from that statement to note exactly the method of checking null in SQL (not bound to any typed check at all):   

> 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

It's not clear what is it you need, but I imagine you need unique nulls (since that's the main gist of the post), though you never explain why.   

> 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

That sentence is either unparsable/misunderstood by me, or false. Could you demonstrate the problem with SQL that doesn't work, and for which there is no suitable way to make it work? We'd be better-able to respond to that.

> 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)

Again not understood - You seem to claim that the thing you want is possible (for triggers to stop inserts for unique matches), and then say you will be happy to be wrong.   
Did you mean to say that there is no way for a trigger to stop the insert where a column of your choice may contain more than one null value? If so then you are wrong, it's a rather easy thing to do.

> 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

SQL is incredibly new next to C, for instance. I bet it will be done exactly the same way again, and this time the Standard would probably be more clear on the silly notion that NULLs can ever be "duplicate". I'd say MSSQL would probably do it different if they had the opportunity to start over.

A claim of "duplicate" means that you can assert that one value (call it A) is exactly the same as another value (B). Only if A == B can be demonstrated, then it can be called "Duplicate" or "Not Unique". When both A and B are unknown, unset, unspecified, indeterminate (pick your favourite word for it) then by definition A cannot possibly be claimed to be exactly equal to B and therefore cannot be duplicate, non-unique, etc.

You can say that you want to insert items that are unknown because of an expectation, say "new" entries the user makes that were not accepted yet. Then the question becomes, Ok, but why do you only want to allow 1 of that and never more? Perhaps the user want to add 3 things so long that are not yet accepted? I'm willing to bet it's purely because MSSQL did not allow any more with their broken system, and so you designed your system that way and now your mindset is hooked on that notion. (That kind of thing certainly happened to me before)

> 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.

That can't be true, right?  Please demonstrate the claim with code examples in an algo language (C, say) and SQL where these differences in meanings of NULL (i.e. "Unset" vs. "Unknown") would produce different mathematically sound outcomes.

> Not allowing the unique unset means interoperability, and data loss issues if pulling data from an sqlserver db or other null unique capable software

Ah, here we get to the real crux of the matter. You are bemoaning the fact that SQLite (and Postgres and MySQL and many more) do not also allow the wrong and mathematically incompetent way that another RDBMS is doing it and so compatibility is somewhat hampered.
This is like calling Tesla and complaining they they do not also support fossil fuel in their cars because the gas station nearest your house doesn't have an EV charger.

To answer the question though, I do very many project cooperations between specifically MSSQL and SQLite, it's never a problem, it's very easy to do, unless the aim is (which I suspect in your case) to make the SQLite table behave exactly like the MSSQL table - in which case, even though I think it's folly, near any of the workarounds already discussed earlier in this thread can accomplish it.

> There is absolutely a valid use for both cases...

Not really, and I posit (from my own mentioned past misconceptions) that the only reason you think that is because you came from MSSQL and found that it works like that and then built previous projects around that expectation, adjusting your thinking to comply, and then being thoroughly surprised it wasn't so when you encountered another DB engine, like SQLite.  You are simply used to doing it the wrong way, when you really open up your mind to what is technically correct (which I'm hoping to assist with here), you will see that the correct way is better and adjusting your projects to work with the correct way will in the long run help you.

> ...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.

I do not disagree here, perhaps I would add that the benefit is small but I would welcome it if it placed no additional resource-requirement/strain on the SQLite Query engine.  If it's a choice of small added benefit vs. faster Queries, I'm going to always choose the latter, and so should you.

Whether or not this comes to pass, you would do yourself an enormous favour to start designing DB interactions/projects from the expectation that more than one record can be unset/unknown (NULL) in a UNIQUE field in the your tables. Designing things so that it CAN work like that, means that the project/code will be completely compatible between MSSQL and SQLite (and any other DB you like).

Oh and PS: If you will petition MSSQL to improve their system and add a Keyword to their TSQL that would allow multiple NULLS in UNIQUE constraints - I can find you many people here, including myself, who will 100% join and sign that.  :)