http://www.sqlite.org/nulls.html
seems to clarify things for me on this topic. Adam On Wed, Dec 30, 2009 at 9:13 AM, Tobias Hoffmann < lsqlite-l...@thax.hardliners.org> wrote: > Hi, > > I had a hard time to understand the FAQ entry on UNIQUE constraint -- in > the end I had to try out sqlite's behavior myself because the FAQ -- so > maybe the wording can be improved and/or an example added. > > Here a some comments: > > *(26) The SQL standard requires that a UNIQUE constraint be enforced > > even of one or more of the columns in the constraint are NULL, but > > SQLite does not do this. Isn't that a bug?* > So this seems to imply that two NULL values will not violate the > UNIQUEness of two rows in SQlite. [Btw. shouldn't it be "... enforced > even IF one or ..."?] > > > > Perhaps you are referring to the following statement from SQL92: > > > > A unique constraint is satisfied if and only if no two rows in > > a table have the same non-null values in the unique columns. > > > IMHO: as they did't just write ".. have the same values in the unique > columns", the database should only compare those columns that are > non-null when enforcing uniqueness. (just as above -- and as SQlite does > it). > > > > That statement is ambiguous, having at least two possible > > interpretations: > > > Now the confusion begins. > > > > 1. A unique constraint is satisfied if and only if no two rows > > in a table have the same values and have non-null values in > > the unique columns. > > > Shall this mean something like (parenthesis to show parsing precendence) > (no two rows in the table have the same values) and ([they] have > non-null values) ... [after some time I realized: this does not make > much sense. But how else was it meant?] > or > no two rows in a table have (the same values and have non-null values) > in the unique columns. [maybe removing the second "have" would help] > > > > 2. A unique constraint is satisfied if and only if no two rows > > in a table have the same values in the subset of unique > > columns that are not null. > > > So you compare only those columns that are not NULL, right? > Where is the difference to (1)? [this made understanding (1) even more > difficult to me]. > And why does the following paragraph state that that SQLite does not > follow this interpretation, although it seems that this is the > unexpected behavior in the original question? > > > > SQLite follows interpretation (1), as does PostgreSQL, MySQL, > > Oracle, and Firebird. It is true that Informix and Microsoft SQL > > Server use interpretation (2), however we the SQLite developers > > hold that interpretation (1) is the most natural reading of the > > requirement and we also want to maximize compatibility with other > > SQL database engines, and most other database engines also go with > > (1), so that is what SQLite does. > > > After all I tried with SQLite and found out that you can have two rows > with NULL in the same (unique-constraint) column. > But I'm not sure if this is really the point of the question, as I still > haven't understood (2) [and don't have MSSQL to test] - or whether its > [wild guess:] about certain behavior with multi-column indices. > > If this is clear to everybody except me, I would appreciate a hint... > otherwise please consider clarifying this FAQ. > > Tobias > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
