9

I am looking for how to implement unique constraints with NULL check.

MySQL shouldn't allow multiple null value.

Employee:

id | name
---|-----
1  | null
2  | null -> should give error during inserting  2nd row.
Moinuddin Quadri
  • 46,825
  • 13
  • 96
  • 126
kabinarayan dalei
  • 95
  • 1
  • 1
  • 5

3 Answers3

10

MySQL 5.7 does allow for a workaround:

mysql> CREATE TABLE `null_test` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `const` varchar(255) NOT NULL DEFAULT '',
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

With soft deletes, it would be nice if you could have just one row with a with a deleted_at = NULL per constraint.

mysql> ALTER TABLE `null_test` ADD `vconst` int(1) GENERATED ALWAYS AS (((NULL = `deleted_at`) or (NULL <=> `deleted_at`))) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

So I created a virtual column that will flip from 1 to null when deleted_at gets set.

mysql> ALTER TABLE `null_test` ADD UNIQUE KEY `nullable_index` (`const`,`vconst`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Instead of including deleted_at to the unique constraint add the virtual column, vconst.

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+------------+--------+
| id     | const | deleted_at | vconst |
+--------+-------+------------+--------+
| 999901 | Ghost | NULL       |      1 |
+--------+-------+------------+--------+
1 row in set (0.01 sec)

No need to insert the vconst (but you cannot, anyhow).

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
ERROR 1062 (23000): Duplicate entry 'Ghost-1' for key 'nullable_index'

Inserting it again throws the Duplicate entry error.

mysql> UPDATE `null_test` SET `deleted_at` = NOW() WHERE `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Same with setting delete_at, no need to touch vconst, it will flip automatically.

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+---------------------+--------+
| id     | const | deleted_at          | vconst |
+--------+-------+---------------------+--------+
| 999901 | Ghost | 2017-02-16 22:07:45 |   NULL |
+--------+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO `null_test` SET `const` = 'Ghost';
Query OK, 1 row affected (0.00 sec)

Now you are free to insert a new row with the same constraints!

mysql> SELECT * FROM `null_test` WHERE `const` = 'Ghost';
+--------+-------+---------------------+--------+
| id     | const | deleted_at          | vconst |
+--------+-------+---------------------+--------+
| 999901 | Ghost | 2017-02-16 22:07:45 |   NULL |
| 999903 | Ghost | NULL                |      1 |
+--------+-------+---------------------+--------+
2 rows in set (0.01 sec)

In this case, depending on how much you soft delete, setting deleted_at, you might want to include deleted_at to the index, or a new index with it, but I will let my load tests decide.

Nothus
  • 1,127
  • 8
  • 7
  • `ALTER TABLE table_name ADD v_column int(1) GENERATED ALWAYS AS (((NULL = deleted_at) or (NULL <=> deleted_at))) VIRTUAL;` That and using the v_column in the unique index is what I needed. – Carl Kroeger Ihl Oct 07 '21 at 00:30
9

No, MySQL is doing the right thing, according to the SQL-99 specification.

https://mariadb.com/kb/en/sql-99/constraint_type-unique-constraint/

A UNIQUE Constraint makes it impossible to COMMIT any operation that would cause the unique key to contain any non-null duplicate values. (Multiple null values are allowed, since the null value is never equal to anything, even another null value.)

If you use a UNIQUE constraint but don't want multiple rows with NULL, declare the columns as NOT NULL and prohibit any row from having NULL.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 5
    Well, my problem is that I want to define a unique key over two columns, where I want to have only a unique combination. And this situation allows me to have (NULL, NULL) multiple times, which is not unique for my needs. So this situation is a little bit tricky. – Jan Krupa Dec 12 '19 at 08:04
1
alter table yourtable add column `virtual_null` varchar(20)  GENERATED ALWAYS AS (if(isnull(`your_nullable_column`),'null',`your_nullable_column`))) VIRTUAL;

alter table yourtable add constraint unique(virtual_null);

Make this and be happy, behind the scenes mysql's null is a hash value. Because that its impossible compare two null values...

Sorry by poor english, good luck

David Meth
  • 11
  • 1