> 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);
--
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/
