How to properly SET NULL on a delete with two attributes referenced?

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

Browse pgsql-novice by date

  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"