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