On Fri, Mar 02, 2012 at 02:21:19PM +0100, Benoit Mortgat scratched on the wall: > On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich <j...@kreibi.ch> wrote: > > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies > > scratched on the wall: > > > > ??Kind of. ??It implies uniqueness in the SQL sense, which does not > > ??include NULLs (remember, NULL != NULL). > > Actually, NULL != NULL is unknown.
As an expression, yes (if you consider NULL to be "unknown"). As a general statement, not really. Setting aside the theoretical argument of what, exactly, NULL means (e.g. "unknown" or something else), it is true that the SQL expression "NULL != NULL" will evaluate to "NULL", not "true." However, I was only trying to make a general statement that "one NULL is not equal to another," in the sense that "NULL == NULL" will not evaluate to "true"... which means it will not trip a UNIQUE constraint. This is why the PK constraint normally implies both UNIQUE and NOT NULL. Not only do NULLs not make sense in a PK as a fundamental identifier, allowing NULLs also breaks the concept that a PK should have a *known* unique (i.e. no NULLs allowed in comparisons) value for each row. SQLite does not imply NOT NULL when you specify a PK constraint. This is in contradiction to the SQL standard, but has been wrong so long nobody wants to risk changing it. Hence, if you want your SQLite PKs to have known unique values, you must specify not only PK, but explicitly specify NOT NULL. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
