Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> writes: > But according to: > http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064 > "The definition of unique constraints in the SQL standards specifies > that the column definition shall not allow null values.", although that > doesn't literally mean NULL values in unique indexes are not allowed... Sybase is wrong here, or at least pretty misleading. SQL92 does allow minimal SQL implementations to impose such a restriction: 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If PRIMARY KEY or UNIQUE is specified, then the <column defi- nition> for each column whose <column name> is in the <unique column list> shall specify NOT NULL. But if you don't enforce that, the spec clearly requires you to accept rows that are duplicate but contain nulls. 11.7 <unique constraint definition> sayeth: 3) Case: a) If the <unique specification> specifies PRIMARY KEY, then let SC be the <search condition>: UNIQUE ( SELECT UCL FROM TN ) AND ( UCL ) IS NOT NULL b) Otherwise, let SC be the <search condition>: UNIQUE ( SELECT UCL FROM TN ) [ UCL = unique column list, TN = table name --- tgl ] ... 2) The unique constraint is not satisfied if and only if EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) ) is true. and the UNIQUE predicate (a thing we don't currently implement btw) is defined in 8.9: <unique predicate> ::= UNIQUE <table subquery> 1) Let T be the result of the <table subquery>. 2) If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the cor- responding column in the other row according to Subclause 8.2, "<comparison predicate>", then the result of the <unique predi- cate> is true; otherwise, the result of the <unique predicate> is false. It says "each column" has to be non-null --- so a row containing any nulls is simply not able to cause a violation of a UNIQUE constraint. Your other quotes show that a number of implementations get this wrong :-(. Date and Darwen read it the same way we do, though (see pages 248 and 254 in A Guide to the SQL Standard, 4th edition), so I have confidence that our reading is correct. regards, tom lane
