Alban Hertroys <alban@magproductions.nl> 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
