title: Unique (multiple columns) and null in one column
body: <p>I have simple categories table. Category can have parent category (<code>par_cat</code> 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.</p>
  
  <p>Code for this table:</p>
  
  <pre><code>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`);
  </code></pre>
  
  <p>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 <code>par_cat</code> set to <code>null</code> 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 <code>par_cat</code> (for example 1 assuming category with id 1 exists), only first record will be inserted (and that's desired behaviour).</p>
  
  <p>Question - how to handle this case? I read that:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>however if I have unique on multiple columns I expected it's not the case (only <code>par_cat</code> can be null, <code>name</code> and <code>url</code> cannot be null). Because <code>par_cat</code> references to <code>id</code> of the same table but some categories don't have parent category it should allow <code>null</code> values.</p>