Subject Re: [ib-support] Unique key that accepts null
Author Claudio Valderrama C.
"Brad Pepers" <brad@...> wrote in message
news:01081013071300.07688@......
>
> Just a BTW, this is also something I miss and I wish it were allowed in
> Interbase. Its not a strange requirement to have a field which must be
> unique but also NULL and having to write triggers for it makes it harder
to
> support (special code only for Interbase). Any idea if this is a
fundamental
> limit of Interbase or just a design choice? From the definition of what
NULL
> is, it doesn't seem right that a unique column shouldn't be allowed to
have
> multiple NULL's (since in SQL two NULL columns are *not* equal to each
other).

What you or me think about our favorite behavior is irrelevant since the SQL
standard has something to say: a PK cannot accept NULL values. A unique
constraint should accept only one NULL value. Again, it's not worth the time
to discuss that if one NULL can fit, all of them should be allowed. The
standard says one NULL at most. So, current fault is that Unique and PK
behave exactly the same. The implicacy of changing this is that we would
have to review if we allow a unique constraint to be the target for a FK
reference field. For now, it's allowed, but if it can have a blank, we have
to ask the SQL standard what do to with the FK field in the detail.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the InterbaseĀ® WebRing