To be honest I'm a bit confused about the wording of these two bullets
too. But here're my comments and clarifications:
>> 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).
Be careful, everything depends here on the meaning of "the same
values". From the human POV all NULLs are the same (probably with the
differentiation per datatype). From the SQL point of view comparison
NULL = NULL is always false, so every NULL is unique and is not the
same as any other NULL (although comparison NULL != NULL is always
false too).
And probably author of this FAQ meant to say exactly this: you can
treat uniqueness and term "same values" as humans do (and as MS SQL
does) or as SQL does (and as SQLite does). For me wording of the
standard is pretty clearly means to treat NULLs as SQLite does it. I
can't even imagine how someone can understand it in another way.
And here're tests showing this issue:
SQLite version 3.6.14.2
sqlite> create table t (a int, b int, unique (a,b));
sqlite> insert into t values (1,2);
sqlite> insert into t values (1,2);
SQL error: columns a, b are not unique
sqlite> insert into t values (1,null);
sqlite> insert into t values (1,null);
sqlite> insert into t values (null,null);
sqlite> insert into t values (null,null);
sqlite> select * from t;
1|2
1|
1|
|
|
sqlite>
sqsh-2.1.4 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2006 Michael Peppler
1> create table #t (a int, b int, unique (a,b))
2> go
1> insert into #t values (1,2)
2> go
(1 row affected)
1> insert into #t values (1,2)
2> go
Violation of UNIQUE KEY constraint 'UQ__#t________________42ACE4D4'.
Cannot insert duplicate key in object 'dbo.#t'.
The statement has been terminated.
1> insert into #t values (1,null)
2> go
(1 row affected)
1> insert into #t values (1,null)
2> go
Violation of UNIQUE KEY constraint 'UQ__#t________________42ACE4D4'.
Cannot insert duplicate key in object 'dbo.#t'.
The statement has been terminated.
1> insert into #t values (null,null)
2> go
(1 row affected)
1> insert into #t values (null,null)
2> go
Violation of UNIQUE KEY constraint 'UQ__#t________________42ACE4D4'.
Cannot insert duplicate key in object 'dbo.#t'.
The statement has been terminated.
1> select * from #t
2> go
a b
----------- -----------
NULL NULL
1 NULL
1 2
(3 rows affected)
1>
Pavel
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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
