>> On 24-4-2012 10:18, Thomas Steinmaurer wrote:
>>>> Is there any way to enforce uniqueness of all non-nulls in a nullable
>>>> column?
>>>
>>>> The "business requirement" would be "this kind of thing has an optional
>>>> attribute, that, if it exists, is unique". For example, a company may or
>>>> may not exist in the Byro van Dijk company database, but if it does
>>>> exist it will have a unique BvD id. I'd like to enforce this uniqueness.
>>>
>>> I'm sure I don't understand the question properly, because a Firebid
>>> UNIQUE constraint on a null-able column doesn't take NULLs into account,
>>> as long as it isn't a compound UNIQUE constraint made up from> 1 columns.
>>
>> Even it is compound, the NULLs are not taken into account for
>> uniqueness, only the non-null parts:
>
> Nope, AFAIK never has been. Try:
>
> create table t2 (c1 varchar(20), c2 varchar(20));
> alter table t2 add constraint u_t2 unique (c1, c2);
>
> commit;
>
> insert into t2 (c1, c2) values ('a', null);
> insert into t2 (c1, c2) values ('a', null);
Can I delete this post, because this one was the most nasty/wrong one
for many years? Sorry. ;-)
--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist
http://www.upscene.com/
Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/
