Subject Re: [firebird-support] Uniqueness among non-null values in nullable column?
Author Thomas Steinmaurer
> Den 2012-04-24 10:18 skrev Thomas Steinmaurer såhär:
>>> 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..
> Oh!? I didn't know. RTFM then... :-) I was working in SQL Server and
> then this cropped up.

Yeah, welcome in the world writing applications supporting multiple
backends. Little, neat differences along different products. ;-)


> It didn't cross my mind that it could be
> different... SQL Server UNIQUE allows only a single null. I really think
> this should be optional, e.g. like this:
> 1. constraint X unique (Y) ignore null
> 2. constraint X unique (Y) restrict null
> 1 would be like FB, 2 like SQL Server.