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.