[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?
On Wed, 27 Jan 2016 14:38:41 -0500 Richard Hipp wrote: > > See https://www.sqlite.org/nulls.html > The most important part to me, is this one: > The fact that NULLs are distinct for UNIQUE columns but are indistinct for > SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs > should be either distinct everywhere or nowhere. And the SQL standards > documents suggest that NULLs should be distinct everywhere. Yet as of > this writing, no SQL engine tested treats NULLs as distinct in a SELECT > DISTINCT statement or in a UNION. SQLite is pragmatic, that's fair enough and this paragraph shows this decision was with awareness of the semantic issue. Hard to do better?? Thanks for your insight to both of you. -- Yannick Duch?ne
[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?
On Wed, 27 Jan 2016 14:17:49 -0500 Igor Tandetnik wrote: > On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: > > `UNIQUE` constraint allows multiple NULL: is this expected? > > http://www.sqlite.org/lang_createtable.html > "For the purposes of UNIQUE constraints, NULL values are considered > distinct from all other values, including other NULLs." > > I'm reasonably, but not 100%, sure this is standard-conforming. Thanks Igor. I overlooked it (and now feel to remember I use to know it and was as much surprised as I am now). Searching the web, it seems most DB don't allow multiple `NULL` on a column with a `UNIQUE` constraint. If the standard says any `NULL` differs from any `NULL`, so be it and these DB are wrong. Just that now I wonder the why. Is this related to something I don't know about, named `UNKNOWN` in SQL? If it's related, then I may understand. -- Yannick Duch?ne
[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?
Hi people out there,
I initially subscribed for another question (which I will post later), when I
came to something which surprised me.
Given this:
CREATE TABLE test (value TEXT UNIQUE)
On this:
INSERT INTO test (value) VALUES('A');
INSERT INTO test (value) VALUES('A')
? the second statement triggers an error due to a `UNIQUE` constraint
violation, as expected.
However, this can be repeated many times:
INSERT INTO test (value) VALUES(NULL)
INSERT INTO test (value) VALUES(NULL)
INSERT INTO test (value) VALUES(NULL)
-- And so on??
? it does not trigger any error.
Is this expected? Well, one may understand it as ?is this expected for SQLite?,
so I prefer two questions instead:
* Is this expected for SQLite?
* it this expected for standard SQL?
(the latter is also a link to another other question to be posted)
--
Yannick Duch?ne
[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?
On 1/27/16, Yannick Duch?ne wrote: > On Wed, 27 Jan 2016 14:17:49 -0500 > Igor Tandetnik wrote: > >> On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: >> > `UNIQUE` constraint allows multiple NULL: is this expected? >> >> http://www.sqlite.org/lang_createtable.html >> "For the purposes of UNIQUE constraints, NULL values are considered >> distinct from all other values, including other NULLs." >> >> I'm reasonably, but not 100%, sure this is standard-conforming. > > Thanks Igor. I overlooked it (and now feel to remember I use to know it and > was as much surprised as I am now). > > Searching the web, it seems most DB don't allow multiple `NULL` on a column > with a `UNIQUE` constraint. If the standard says any `NULL` differs from any > `NULL`, so be it and these DB are wrong. Just that now I wonder the why. > See https://www.sqlite.org/nulls.html > Is this related to something I don't know about, named `UNKNOWN` in SQL? If > it's related, then I may understand. > > -- > Yannick Duch?ne > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?
On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: > `UNIQUE` constraint allows multiple NULL: is this expected? http://www.sqlite.org/lang_createtable.html "For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs." I'm reasonably, but not 100%, sure this is standard-conforming. -- Igor Tandetnik
