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.

e.g., for inserts,
if (exists(select aValue from aTable where aValue=new.aValue)) then
raise an error
or, for updates
if (new.value <> old.value) then
if (exists(select aValue from aTable where aValue=new.aValue)) then
raise an error

Regards,
Helen
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________