Date: Thu, 3 Jun 2004 07:24:02 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Reference with condition on other table column?
In-Reply-To: <20040603091627.258131E338@ar-sd.net>
Message-ID: <20040603071816.Y83422@megazone.bigpanda.com>
References: <20040603091627.258131E338@ar-sd.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 3 Jun 2004, Andrei Bintintan wrote:

> Hi to all,
>
> I have the following tables:
> CREATE TABLE t1(
> id serial PRIMARY KEY,
> active boolean NOT NULL DEFAULT 'y',
> num int4 NOT NULL,
> );
> CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active;
>
> CREATE TABLE t2(
> id serial PRIMARY KEY,
> active boolean NOT NULL DEFAULT 'y',
> num int4 NOT NULL,
> );
> CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active;
>
>
> CREATE TABLE relations(
> id serial PRIMARY KEY,
> id_t1 int4 NOT NULL REFERENCES t1(num),
> id_t2 int4 NOT NULL REFERENCES t2(num)
> );
>
> On tables T1 and T2 the "num" columns have unique values for all lines that
> have active='y'(true).
>
> How can I write a constraint on Table T1 and Table T2 that if the "num" from
> T1 and "num" from T2 are referenced from table "relation" than I cannot
> update the "active" field to "false". My target is that I don't want to have
> any reference from "relation" table to T1 and T2 where in the T1 and T2 the
> active field is "n"(false)
>
> or with other words:
>
> if a line from T1/T2 is referenced from table "relations" than I don't want
> to be able to put active='y'.

Hmm, I can see is having two other tables that you reference that have
rows containing num added/removed by triggers when T1 or T2 are changed,
so that inserting an active='y' row inserts a row into the appropriate
one, update a ='n' -> 'y' inserts a row, update 'y'->'n' removes a row and
deleting a ='y' row removes a row.  That might get messy though.