I wanted to know whether there are any versions that allow multiple NULL on UNIQUE. Also, single NULL for UNIQUE. Please specify the versions.

  • 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 – user98890 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

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:

  ON tableX
  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:

  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

| improve this answer | |

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