Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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



pgsql-novice by date

Next:From: Donald KerrDate: 2010-11-12 21:18:05
Subject: Re: Postgres Wishlist
Previous:From: Tom LaneDate: 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"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group