Svein Erling Tysvær wrote:

> If the constraint could include uniqueness for the combination TEXTID,
> ENDDATE even if ENDDATE wasn't NULL, then you could probably make your
> own workaround - even in InterBase:
>
> Add another unique column, e.g. ENDDATENOTNULL, that you populate using
> BEFORE triggers containing code like:
>
> new.ENDDATENOTNULL = coalesce(new.ENDDATE, '01.01.1900')
>
> (not knowing whether InterBase supports coalesce, if not it is a simple
> IF statement - I assume no records in your database will ever have
> 01.01.1900 as their enddate)
>
> ENDDATENOTNULL would then never be NULL and you could create your
> constraint on TEXTID, ENDDATENOTNULL.

This suggestion was along the lines I thought, but I was thinking a
constraint on a computed by column with coalesce on the nullable one
would work:

alter table X
add ENDDATENOTNULL computed by (coalesce(ENDDATE, '01.01.1900'));

And then

alter table X
add constraint Y unique (TEXTID, ENDDATENOTNULL);

But I was surprised to learn that computed by columns can't be indexed.
I thought this was possible and thought I could recall that from release
notes or something. Is it actually not possible, and what might it be
that I'm thinking of...?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64