Thanks Helen,

Would the trigger slows down performance noticeably? Since we are using
Exists here to test the whole table records' values.

My purpose here is to allow users to key in barcode numbers for items that
they keep, but some items are without barcode numbers, so I have to allow
null.

Best wishes,
jr

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Friday, August 10, 2001 3:53 PM
Subject: Re: [ib-support] Unique key that accepts null


> 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
> _______________________________________________________
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>