No big deal, but I had to look at this recently, so I though I'd point it out before I forget about it.
First, according to the SQLite documentation for CREATE INDEX: "If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard." But Oracle says this: "To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint." Which contradicts what SQLite says, at least for multi-column unique constraints. So I'd just drop Oracle from that list, since Oracle only treats NULLs as unique when all columns in the unique constraint are NULL. (Which, if anyone should happen to ask,I thin is a bit weird and counter-intuitive, but that's just me.) Peter _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
