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
