Subject Re: [firebird-support] Uniqueness among non-null values in nullable column?
Author Kjell Rilbe
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. 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.


Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

[Non-text portions of this message have been removed]