car.toper wrote:

>
>> And, BTW, this is not Interbase support list.
>
> Well, it was my impression that firebird was Interbase, just the free
> version. I guess i will seek help elsewhere for Interbase related

questions.

>

No, feel free to ask here. Firebird is a fork of Interbase, which
separated nine years ago, so the two have drifted apart. But there
are people here who know InterBase pretty well. However, the constraint
you're asking for doesn't exist, as far as I can parse the standard.
What you want is almost a UNIQUE constraint, but the comparison rules
there consider each NULL to be a distinguishing value.

From the 2003 standard, <unique predicate>:

2) If there are no two rows in T such that the value of each
column in one row is non-null and is not distinct from the
value of the corresponding column in the other row, then
the result of the <unique predicate> is True; otherwise,
the result of the <unique predicate> is False.

What I would suggest is using a unique constraint to sort out the
majority of cases, and a trigger that throws out the cases where
two rows are equal and have paired NULL fields. It's not perfect
because of visibility rules, but it will come pretty close.

Aside to other Firebirdies - lets help InterBase users see the
benefits of a community as well as the cost advantages of open source
software.


Good luck,

Ann