Unique constraint with nullable column
- by Álvaro G. Vicario
I have a table that holds nested categories. I want to avoid duplicate names on same-level items (i.e., categories with same parent). I've come with this:
CREATE TABLE `category` (
`category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_name` varchar(100) NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`category_id`),
UNIQUE KEY `category_name_UNIQUE` (`category_name`,`parent_id`),
KEY `fk_category_category1` (`parent_id`,`category_id`),
CONSTRAINT `fk_category_category1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
Unluckily, category_name_UNIQUE does not enforce my rule for root level categories (those where parent_id is NULL). Is there a reasonable workaround?
