Using Constraints on Hierarchical Data in a Self-Referential Table
- by pbarney
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 update on cascades and prevent deletion of fruit if they have "children."
So I used the following:
CREATE TABLE `idtlp_main`.`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 (`iddoc_main`.`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.
