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 (
primary key( id )
create table node (
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!
pgsql-novice by date
|Next:||From: Donald Kerr||Date: 2010-11-12 21:18:05|
|Subject: Re: Postgres Wishlist|
|Previous:||From: Tom Lane||Date: 2010-11-12 21:07:39|
|Subject: Re: login error - Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user "kamik" |