Subject Re: [firebird-support] Uniqueness among non-null values in nullable column?
Author Thomas Steinmaurer
> On 24-4-2012 10:41, Thomas Steinmaurer wrote:
>>> 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);
> I think we misunderstood each other. My comment was on the fact that you
> claim - or at least so it seemed to me - that NULLs in unique keys only
> work for non-compound keys, which isn't true.

Correct and I hope I didn't claim that. ;-)

SQL Server is different when it comes to handling NULLs in unique
constraints. Oracle and Firebird behave the same way. Haven't checked
how MySQL or PostgreSQL are doing in that area.

A few years ago, in a project we had to support Oracle (server) and SQL
Server (MSDE; locally) and these little differences drove us crazy from
a physical data model POV. Finally they settled with Oracle XE instead
of MSDE locally, which made things much more easier.

Firebird wasn't an option in that project.

With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

Do you care about the future of Firebird? Join the Firebird Foundation: