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.
我有简单的类别表。类别可以具有父类别（par_cat列），如果它是主类别，则为null，并且具有相同的父类别，不应该有2个或更多具有相同名称或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).
问题是，即使我有唯一的密钥，它也不起作用。如果我尝试将par_cat设置为null且名称和URL相同的数据库中插入2个类别，那么这2个类别可以毫无问题地插入到数据库中（并且它们不应该）。但是，如果我选择其他par_cat类别（例如1假设存在id为1的类别），则只会插入第一条记录（这是所需的行为）。
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.
UNIQUE索引创建一个约束，使索引中的所有值必须是不同的。如果您尝试添加具有与现有行匹配的键值的新行，则会发生错误。此约束不适用于除BDB存储引擎之外的NULL值。对于其他引擎，UNIQUE索引允许包含NULL的列的多个NULL值。如果为UNIQUE索引中的列指定前缀值，则列值在前缀中必须是唯一的。
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.
但是如果我在多列上有唯一性，我预计情况并非如此（只有par_cat可以为null，name和url不能为null）。因为par_cat引用同一个表的id，但某些类别没有父类，所以它应该允许空值。
1 个解决方案
#1
22
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.)
这符合SQL标准的定义。 NULL表示未知。如果你有两个par_cat = NULL和name ='X'的记录，那么这两个NULL不会被认为具有相同的值。因此，它们不违反唯一键约束。 （好吧，有人可能会争辩说NULL仍然可能意味着相同的值，但是应用这个规则会使得使用唯一索引和可空字段几乎是不可能的，因为NULL也可以意味着1,2或其他任何值。所以它们做得很好如我们所做的那样定义它。）
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.
由于MySQL不支持功能索引，你可以在ISNULL（par_cat，-1），name上有索引，你唯一的选择是使par_cat成为一个带有0或-1的NOT NULL列或者对于“无父”的任何内容，如果你希望你的约束能够奏效。
#1
22
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.)
这符合SQL标准的定义。 NULL表示未知。如果你有两个par_cat = NULL和name ='X'的记录，那么这两个NULL不会被认为具有相同的值。因此，它们不违反唯一键约束。 （好吧，有人可能会争辩说NULL仍然可能意味着相同的值，但是应用这个规则会使得使用唯一索引和可空字段几乎是不可能的，因为NULL也可以意味着1,2或其他任何值。所以它们做得很好如我们所做的那样定义它。）
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.
由于MySQL不支持功能索引，你可以在ISNULL（par_cat，-1），name上有索引，你唯一的选择是使par_cat成为一个带有0或-1的NOT NULL列或者对于“无父”的任何内容，如果你希望你的约束能够奏效。