Subject Re: [ib-support] Unique key that accepts null
Author Ivan Prenosil
Below is older message from SQL master.
(note that IB documentation claims conformance to Entry level)


Date: Wed, 7 Mar 2001 18:23:35 -0500
From: "Brown, Diane" <Diane.Brown@...>
To: "'interbase@...'" <interbase@...>
Subject: RE: unique constraint failure

Fred Wilson wrote:

>A unique field has to be NOT NULL, regardless of the fact that NULL !=
>NULL.. Remember, NULL is a *state*, NOT a value.. In order for
>a column to
>be unique, IB needs to know it's VALUE, right ?? If IB can't
>determine it's
>VALUE, how can it know whether it's unique or not ??

["it's VALUE" .... argghhhhhhhhhh .... there, I feel better now :-) ]

Well, that may very well be true, but the SQL standard says:

"A unique constraint is satisfied if and only if no two rows in a table have
the same non-null values in the unique columns. In addition, if the unique
constraint was defined with PRIMARY KEY, then it requires that none of the
values in the specified column or columns be the null value."

In other words, in SQL92, UNIQUE constraint first eliminates nulls then
checks for duplicates.

*****HOWEVER******, there is a leveling rule in SQL92, subclause 11.7
<unique constraint definition>, that says:

"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 definition> for
each column whose <column name> is in the <unique column list> shall specify

And snookered you are.