Unique (multiple columns) and null in one column

Question

I have simple categories table. Category can have parent category (par_cat column) or null if it is main category and with the same parent category there shouldn't be 2 or more categories with the same name or url.

Code for this table:

CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL,   `par_cat` int(10) unsigned DEFAULT NULL,   `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'pl',   `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,   `url` varchar(120) COLLATE utf8_unicode_ci NOT NULL,   `active` tinyint(3) unsigned NOT NULL DEFAULT '1',   `accepted` tinyint(3) unsigned NOT NULL DEFAULT '1',   `priority` int(10) unsigned NOT NULL DEFAULT '1000',   `entries` int(10) unsigned NOT NULL DEFAULT '0',   `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',   `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;    ALTER TABLE `categories`   ADD PRIMARY KEY (`id`),    ADD UNIQUE KEY `categories_name_par_cat_unique` (`name`,`par_cat`),    ADD UNIQUE KEY `categories_url_par_cat_unique` (`url`,`par_cat`),    ADD KEY `categories_par_cat_foreign` (`par_cat`);   ALTER TABLE `categories`   MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;  ALTER TABLE `categories`ADD CONSTRAINT `categories_par_cat_foreign`    FOREIGN KEY (`par_cat`) REFERENCES `categories` (`id`); 

The problem is that even if I have unique keys it doesn't work. If I try to insert into database 2 categories that have par_cat set to null and same name and url, those 2 categories can be inserted into database without a problem (and they shouldn't). However if I select for those categories other par_cat (for example 1 assuming category with id 1 exists), only first record will be inserted (and that's desired behaviour).

Question - how to handle this case? I read that:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

however if I have unique on multiple columns I expected it's not the case (only par_cat can be null, name and url cannot be null). Because par_cat references to id of the same table but some categories don't have parent category it should allow null values.

Answer 1

This works as defined by the SQL standard. NULL means unknown. If you have two records of par_cat = NULL and name = 'X', then the two NULLs are not regarded to hold the same value. Thus they don't violate the unique key constraint. (Well, one could argue that the NULLs still might mean the same value, but applying this rule would make working with unique indexes and nullable fields almost impossible, for NULL could as well mean 1, 2 or whatever other value. So they did well to define it such as they did in my opinion.)

As MySQL does not support functional indexes where you could have an index on ISNULL(par_cat,-1), name, your only option is to make par_cat a NOT NULL column with 0 or -1 or whatever for "no parent", if you want your constraints to work.

Answer 2

I see that this was asked in 2014. However it is often requested from MySQL: https://bugs.mysql.com/bug.php?id=8173 and https://bugs.mysql.com/bug.php?id=17825 for example. People can click on affects me to try and get attention from MySQL.

Since MySQL 5.7 we can now use the following workaround:

ALTER TABLE categories  ADD generated_par_cat INT UNSIGNED AS (ifNull(par_cat, 0)) NOT NULL, ADD UNIQUE INDEX categories_name_generated_par_cat (name, generated_par_cat),  ADD UNIQUE INDEX categories_url_generated_par_cat (url, generated_par_cat);  

The generated_par_cat is a virtual generated column, so it has no storage space. When a user inserts (or updates) then the unique indexes cause the value of generated_par_cat to be generated on the fly which is a very quick operation.