Hello experts!
I'm having some trouble with the following two tables that a tree of nodes. The rules I want to enforce are these:
R1) Each node belongs to a tree
R2) A node may or may not have a single parent node.
R3) The parent of a node must belong to the same tree as the child.
R4) A node may not have itself as a parent.
My first attempt fails, and I wasn't too surprised...
create table tree (
id int,
primary key( id )
);
create table node (
id int,
tree int,
parent int, -- may be null, R2
primary key( id, tree ), -- R1
foreign key( parent, tree ) references node( id, tree ) on update cascade, -- R3, ** obvious problem here
constraint parent_cycle check( parent != id ) -- R4
);
The thing is that I still want a cascade if the id of a parent node is updated.
But I can see that I need to nullify the parent attribute if the parent is deleted, but I don't want
to nullify the entire foreign key (parent, tree). Just the parent. I did try this:
foreign key( parent, tree ) references node( id, tree ) on delete set null,
But, unfortunately, the tree component of the foreign key is indiscriminately nulled.
As always, help greatly appreciated!
- Leon