At 03:07 PM 12/11/2003 -0500, you wrote:

>If I create a unique, multi-column index will inserts fail on a NULL ==
>NULL match?
>
>That is:
>
> > create unique index foo on bar( cola, colb, colc);
> > insert into bar ( cola) values ('a');
> > insert into bar ( cola) values ('a');
>
>Should the second insert fail?

No.

>(The answer appears to be yes, but I'm hoping there is a way to set this up
>so that the answer is no).

Null is not a value. Null==Null always returns False (no match).
You can insert this row a gazillion times if you want to:
insert into bar ( cola) values (null, null. null);

The original blah on the evaluation sequence in the release notes was a
valiant attempt (and I mean VALIANT) by the author of the code to interpret
the standard. Unfortunately, like the standard, it was incomprehensible to
English and non-English speakers alike. Simplified (but still needing a
lot of concentration!), it says:

<unique constraint definition> ::=
<unique specification> ( <unique column list UCL> )
<unique specification> ::= {UNIQUE | PRIMARY KEY}

The constraint allows existence of only those rows for which search
condition (i) or (ii) evaluates as True, according to the following logic:

i) If the <unique specification> specifies PRIMARY KEY, then the
search condition shall be:

UNIQUE ( SELECT UCL FROM TN ) AND ( UCL ) IS NOT NULL

ii) Otherwise, the <search condition> shall be:

UNIQUE ( SELECT UCL FROM TN )

In this case, the condition UNIQUE can not be True if ( SELECT UCL FROM TN
) could output two rows having all segments non-null and all of the
corresponding segments matching.

The constraint allows existence of only those rows for which the
aforementioned <search condition> evaluates to True. It means that the
PRIMARY KEY constraint doesn't allow NULLs whilst the UNIQUE constraint
allows an arbitrary number of NULLs. For multi-column result sets of (
SELECT UCL FROM TN ), the common rules for NULLs are applied, i.e. (1,
NULL) is distinct from (NULL, 1) and one (NULL, NULL) is distinct from any
other (NULL, NULL).

heLen