I wanted to know whether there are any versions that allow multiple NULL on UNIQUE. Also, single NULL for UNIQUE. Please specify the versions.
closed as unclear what you're asking by hot2use, Philᵀᴹ, Md Haidar Ali Khan, mustaccio, Max Vernon Jul 31 '18 at 15:43
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
All versions of MySQL and derivatives (MariaDB, Drizzle, Galera, etc.) ignore nulls in
UNIQUE constraints. This allows for any number of rows that have nulls in the columns of a unique constraint. Note that this is the behaviour defined in the SQL standard.
On the opposite, all SQL Server versions, at least from 2000 till now, restrict to only one
NULL in unique constraints. I think there was a Connect request to have this changed but since it would break backwards compatibility (in lots of existing databases that have been using the feature as it is for more than 20 years), I highly doubt this will ever be changed.
If you want to have the standard behaviour (multiple nulls) in SQL Server, you can use a filtered index in versions 2008+. Example:
CREATE UNIQUE INDEX Nulls_ignored_UQ ON tableX (columnA) WHERE (columnA IS NOT NULL) ;
One disadvantage is that you can't use this column as a target for a
FOREIGN KEY constraint (only
PRIMARY KEY and
UNIQUE constraints can be used for that, not unique indexes.)
On the opposite issue, if you want to allow only a single null in MySQL, you can use a computed column in recent versions (MariaDB 5.2+, MySQL 5.7+) and then add a second UNIQUE constraint:
ALTER TABLE tableX ADD columnA_single_null int AS (CASE WHEN columnA IS NULL THEN 1 END), ADD CONSTRAINT Single_null_UQ UNIQUE (columnA_single_null) ;
Test in dbfiddle.uk