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.

[10-things-in-mysql-that-wont-work-as-expected](http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/) — Jaugar Chang, Sep 15 '14 at 10:01
[This answer](http://stackoverflow.com/a/429827/3630826) may explain the problem you have. Suggest you change par_cat to not null, set a minimun id such as 0 for root. And auto increase category id from 1. — Jaugar Chang, Sep 15 '14 at 10:20
@JaugarChang But I also have foreign key constraint for `par_cat` so I cannot use value `0` because there's no record with id 0 — Marcin Nabiałek, Sep 16 '14 at 12:39
Or you could add a root record with id 0. But I don't think it's necessary to use foreign key. You'd better enforce referential integrity on application side not on DB side. — Jaugar Chang, Sep 16 '14 at 12:51

score 44 · Accepted Answer · answered Sep 15 '14 at 10:23

44

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.

answered Sep 15 '14 at 10:23

Thorsten Kettner

89,309
7
49
73

4

Ok, I understand. But if I don't make it NULL, I won't be able to insert into `par_cat` value 0 because of foreign key constraint `FOREIGN KEY (`par_cat`) REFERENCES `categories` (`id`);`. So does it mean that I have to choose between 2 constraints - either use foreign key (with null) and don't use unique or use unique but remove foreign key on `par_cat` because those 2 constraints won't work together? – Marcin Nabiałek Sep 16 '14 at 12:37
6

Or put a dummy record with id 0 into categories. – Thorsten Kettner Sep 16 '14 at 12:40
3

Ok, but I need to put it first before creating foreign key otherwise it couldn't be inserted into database, right? What's the common practice in that case? Put such 0 record (as you suggested) or don't use foreign key or unique key? Using `0 record` make that in case if I want to get everything from DB I need to always remove those `0 record` from results – Marcin Nabiałek Sep 16 '14 at 12:46
5

You are right, there is no perfect solution. I'd put that dummy record in the table, so database integrity is secured by the foreign key constraint. You are right though that it can be annoying to have to remember each time to exclude category 0. You could write a view on that table that excludes the record for convenience. As to common practise: I don't know. A functional index as mentioned in my anwer would be great. If it were available in MySQL that's the road I would choose. – Thorsten Kettner Sep 16 '14 at 14:07

score 25 · Answer 2 · answered Jul 12 '19 at 03:07

25

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.

answered Jul 12 '19 at 03:07

Yoseph

730
1
7
8

2

Thank you! Can confirm that this works thanks to MySQL 5.7's ["Generated Columns" feature](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html). We had the NULL problem with unique-indexing datetimes (soft-deletion :deleted_at column) so rather than turning NULLs to zero, we just stringify `\`stringified_deleted_at\` char(19) GENERATED ALWAYS AS (CONVERT(IFNULL(deleted_at, ""),char)) STORED` – Epigene Aug 29 '19 at 08:16
I would suggest the original poster mark this comment as the accepted answer. It solves the problem using native MySQL 5.7+ functionality, without having to resort to dummy records, etc. – Mark Hall Dec 15 '21 at 16:02

score 6 · Answer 3 · answered Nov 10 '21 at 03:51

Just in case you come from Laravel...

This is Laravel's Migration version for Virtual Column to workaround the UNIQUE issue when one of the columns is NULL in value

$table->integer('generated_par_cat')->virtualAs('ifNull(par_cat, 0)');

$table->unique(['name', 'generated_par_cat'], 'name_par_cat_unique');

Unique (multiple columns) and null in one column

3 Answers3