From: | Leon Starr <leon_starr(at)modelint(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | How to properly SET NULL on a delete with two attributes referenced? |
Date: | 2010-11-12 21:16:59 |
Message-ID: | 281539F2-286D-4037-8466-6D5176D6102C@modelint.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Donald Kerr | 2010-11-12 21:18:05 | Re: Postgres Wishlist |
Previous Message | Tom Lane | 2010-11-12 21:07:39 | Re: login error - Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user "kamik" |