Is it Possible to Use Constraints on Hierarchical Data in a Self-Referential Table?

Question

Suppose you have the following table, intended to represent hierarchical data:

+--------+-------------+
| Field  | Type        |
+--------+-------------+
| id     | int(10)     |
| parent | int(10)     |
| name   | varchar(45) |
+--------+-------------+

The table is self-referential in that the parent_id refers to id.

So you might have the following data:

+----+--------+---------------+
| id | parent | name          |
+----+--------+---------------+
|  1 |      0 | fruit         |
|  2 |      0 | vegetable     |
|  3 |      1 | apple         |
|  4 |      1 | orange        |
|  5 |      3 | red delicious |
|  6 |      3 | granny smith  |
|  7 |      3 | gala          |
+----+--------+---------------+

Using MySQL, I am trying to impose a (self-referential) foreign key constraint upon the data to cascade on update and prevent deletion of a record if it has any "children."

So I used the following:

CREATE TABLE `test`.`fruit` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent` INT(10) UNSIGNED,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent`
    FOREIGN KEY (`parent`)
    REFERENCES `fruit` (`id`)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
)
ENGINE = InnoDB;

From what I understand, this should fit my requirements. (And parent must default to null to allow insertions, correct?)

The problem is, if I change the id of a record, it will not cascade:

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fruit`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE)

What am I missing?

Feel free to correct me if my terminology is screwed up... I'm new to constraints.

Answer 1

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.