Subject Re: [ib-support] Unique key that accepts null
Author Claudio Valderrama C.
"Brad Pepers" <brad@...> wrote in message
> 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
> support (special code only for Interbase). Any idea if this is a
> limit of Interbase or just a design choice? From the definition of what
> is, it doesn't seem right that a unique column shouldn't be allowed to
> multiple NULL's (since in SQL two NULL columns are *not* equal to each

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.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing