NULL is considered different from any other value, including another NULL, in the context of UNIQUE.
So your unique index has 2 entries (1, NULL1) for rowid 1 and (1, NULL2) for rowid2. See https://sqlite.org/nulls.html It also states that NULL1 == NULL2 is TRUE in UNION and DISTINCT contices. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von ingo Gesendet: Mittwoch, 05. Juni 2019 09:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 'where ... isnull' in create index For the second insert in the code below I expected a failure. From the diagrams in the create index doc I understand the WHERE ts_to ISNULL is legal. Do I misunderstand the docs or is there an other place where I should look. I'm awar that I could use some future data as default for ts_to but it is not elegant to me, TIA, ingo ---%<------%<------%<--- CREATE TABLE IF NOT EXISTS person ( pid INTEGER NOT NULL, full_name TEXT, ts_to TEXT DEFAULT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS idx_person ON person (pid, ts_to) WHERE ts_to ISNULL ; INSERT INTO person (pid, full_name) VALUES (1,'pietje puk'); INSERT INTO person (pid, full_name) VALUES (1,'jan tabak'); -----> should fail?? SELECT * FROM person WHERE ts_to ISNULL; _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
