CODEWITHSUNDEEP
mysqlinnodb
Álvaro González
Álvaro González

Reputation: 146390

Unique constraint with nullable column

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?

Upvotes: 4

Views: 1252

Answers (2)

Wrikken
Wrikken

Reputation: 70460

As far as I can see, to enforce is on the database side, possibilities:

  1. Define a 'root' node, only adding to root node is allowed, not a ´new' rootnode, or
  2. Add an before insert of before update trirgger

BTW: on parent delete categories are promoted to root categories, is that what you want?

Upvotes: 2

Unreason
Unreason

Reputation: 12704

Reasonable workaround might include

  • checking constraints with triggers if the update/insert operations are not critical for speed
  • using some sort of special value to denote null; this could be modelled relatively properly - have a root node with id 0 that will never be deleted, have parent_id DEFAULT 0 and ON DELETE SET DEFAULT

Upvotes: 5

Related Questions