implement tree as adjacency list

[Yuwei Zhao]

Hi!

I tried to create a table to model a tree structure:

create table personnel
(emp varchar(20) primary key,
boss varchar(20),
index(boss, emp),
foreign key (boss) references personnel(emp) on update cascade on delete cascade) type = innodb;

insert into personnel values('Albert', null);
insert into personnel values('Bert', 'Albert');
insert into personnel values('Chuck', 'Albert');
insert into personnel values('Donna', 'Chuck');
insert into personnel values('Eddie', 'Chuck');
insert into personnel values('Fred', 'Chuck');

If I try to remove emp 'Albert'

delete from personnel where emp = 'Albert'

it works fine and all records are removed.

But if I try to update a record

update personnel set emp = 'david' where emp = 'Albert';

it fails: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Does anyone have a fix to this problem?

/Wei