Unique zero-column constraint

Question

I have a table containing nested categories. I want to avoid duplicate names on elements of the same level (i.e. Categories with the same parent). I came 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 

Unfortunately, category_name_UNIQUE does not apply my rule for root level categories (those where parent_id is NULL). Is there a reasonable solution?

Answer 1

A smart workaround may include

• checking constraints using triggers if update / insert operations are not critical to speed
• using some special meaning to indicate null; this can be modeled relatively correctly - have a root root with id 0 that will never be deleted, have parent_id DEFAULT 0 and ON DELETE SET DEFAULT

Answer 2

As far as I can see, in order to enforce compliance on the database side the features are:

• Define a “root” node, only add to the root node, not a “new” rootnode, or
• Add before insert before upgrading trirgger

By the way: in parent categories, deletions are promoted to root categories, is that what you want?