2

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.

  • Is the question about SQL Server and MySQL only? Or about any DBMS in general? – ypercubeᵀᴹ Jul 29 '18 at 8:21
  • SQL server and MySQLonly – Ashwani Bhat Jul 29 '18 at 9:34
  • SQL server dupe – Terminus Jul 29 '18 at 14:05
  • What is the ultimate goal? Perhaps we can help find a viable Kludge other than the suggested null-unique one. – Rick James Aug 18 '18 at 2:29
3

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.