checking data integrity in a recursive table

From: "Luke Pascoe" <luke(dot)p(at)kmg(dot)co(dot)nz>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: checking data integrity in a recursive table
Date: 2003-01-23 20:19:51
Message-ID: 019801c2c31c$c8d63fa0$3200000a@K2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi, I have a table that references itself to create a tree-like structure,
eg:
CREATE TABLE tree (
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
parent INT NULL,
customer IN NOT NULL,
CONSTRAINT parent_key...
CONSTRAINT customer_fk FOREIGN KEY (customer) REFERENCES customer
);
ALTER TABLE tree ADD CONSTRAINT FOREIGN KEY (parent) REFERENCES tree;

As you can see tree also references the customer table.

What I need is a CHECK that will ensuer that any given "tree" row has the
same customer as its parent.
Remember that "parent" can also be NULL.

Or would this be better done as a trigger?

TIA!

========================================
Luke Pascoe
KMG (NZ) Limited. http://www.kmg.co.nz
Mobile: (021) 303019
Email: luke(dot)p(at)kmg(dot)co(dot)nz
========================================

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Larry Rosenman 2003-01-23 20:30:31 Re: SQL to list databases?
Previous Message Tom Lane 2003-01-23 19:54:09 Re: To use a VIEW or not to use a View.....