So in my context a customization would be very useful. You can argument that my context is very special. Actually I cannot see so many cases there you want a unique index which is not constrained on null. And for that you can always use a partial index which makes it much more clearer. Anyway it was only an idea and I think I need to convince my company to pay for it which is not so probable. 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. 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. 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. You can check if something is null, not needing to worry about the type in any way.
I would have to have an index for every combination of nulled column, N choose M combinations. 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. 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. 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.
Several workarounds good ones have been demonstrated in this thread, even some by the very poster who originally posed the question. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Asked 3 years, 10 months ago. Active 3 years, 10 months ago.
Viewed times. I use: sqlite-jdbc StackTrace: org. Very neat trick. I solved the problem in the application instead of in the database, but I wanted to put that here for anyone who came after me.
Another option to to use a filtered index stackoverflow. Nice trick. However, the code sample could be improved on by dropping the unnecessary back-ticks or at least replacing them with the standard double quotes and using the standard coalesce Field2,0 instead of ifnull.
Satyam Raikar Satyam Raikar 6 6 silver badges 19 19 bronze badges. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked 7. Related Subscribe for more articles Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Simple Talk. Rate this article.
0コメント