Subject Re: [ib-support] Unique key that accepts null
Author Brad Pepers
On Saturday 11 August 2001 22:12, Claudio Valderrama C. wrote:
> "Brad Pepers" <brad@...> wrote in message
> news:01081013071300.07688@......
> 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.

Where are you getting this from in the SQL standard? I've just been reading
my Guide to The SQL Standard (4th Edition) by C.J. Date and on page 254 under
the effects of NULLs on candidate keys it states:

"Note in particular, therefore, that if the candidate key consists of a
single column - and is not in fact the primary key - then that column can
contain *any number* of nulls (together with any number of nonnull values,
all of which must be distinct)."

This directly contradicts what you have said and shows that the SQL standard
is actually *not* being followed by Firebird.

Brad Pepers