Kjell Rilbe 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.

Thomas Steinmaurer answers:

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.

Mark Rotteveel adds:

Even it is compound, the NULLs are not taken into account for uniqueness, only the non-null parts: https://firebirdsql.org/file/documentation/html/en/firebirddocs/nullguide/firebird-null-guide.html

Like this post? Share on: TwitterFacebookEmail

Related Articles


Firebird Community



Gems from Firebird Support list