Unique (multiple columns) and null in one column

Question

I have a simple category table. A category can have a parent category ( par_cat column) or null if it is the main category, and with the same parent category there should not be two 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, this will not work. If I try to insert 2 categories into the database that have par_cat set to null and the same name and URL, these 2 categories can be inserted into the database without problems (and they should not). However, if I select other par_cat for these categories (for example, 1 if the category with identifier 1 exists), only the first record will be inserted (and this is the desired behavior).

The question is how to handle this. I read that:

A UNIQUE index creates a constraint, so all values ​​in the index must be different. An error has occurred if you try to add a new row using the key value that matches the existing row. This restriction does not apply to NULL values, with the exception of the BDB storage engine. For other engines, the UNIQUE Index allows multiple NULL values ​​for columns that may contain ZERO. If you specify a prefix value for a column in the UNIQUE index, the column values ​​must be unique within the prefix.

however, if I have a unique across several columns, I expected it to not (only par_cat can be null, name and url can not be null). Since par_cat refers to the id the same table, but some categories do not have a parent category, it must be null .

Answer 1

This works as defined by the SQL standard. NULL means unknown. If you have two entries par_cat = NULL and name = 'X', then two NULLs are not considered to have the same value. Thus, they do not violate a single key restriction. (Well, it can be argued that NULL can still mean the same value, but applying this rule will make working with unique indexes and fields with a null value almost impossible, since NULL can also mean 1, 2, or any other value. So define it as it was in my opinion.)

Since MySQL does not support functional indexes, where you may 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 any other for “without parent” if you want so that your limitations work.