On Friday 10 August 2001 01:53, Helen Borrie wrote:

> At 02:32 PM 10-08-01 +0800, you wrote:
> >Hi,
> >
> >Is it possible to create a unique key that accepts null value, just like
> > MS Access 2000? I have tried to create a unique constraint that defaults
> > null value, but failed. My purpose is to make sure that the value is not
> > duplicated and at the same time allow the users to leave it blank.
>
> No, you can't have a unique constraint on a nullable column. You can set a
> default "blank" value (empty string for a character column, zero-equivalent
> for others) if you need to but, of course, on one row will be allowed to
> exist containing the default so you would need a trigger to apply a unique
> new value on your BEFORE events.
>
> Do you have any other actual purpose in wanting this column to be nullable?
> If you want to enforce a rule that says "It's OK for the column to be NULL
> but not OK to enter an actual value that already exists", then remove the
> UNIQUE constraint and write BEFORE triggers to enforce the rule.

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).

--
Brad Pepers
brad@...