Date: Thu, 3 Jun 2004 13:20:25 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Reference with condition on other table column?
Message-ID: <20040603182025.GB5022@wolff.to>
Mail-Followup-To: Andrei Bintintan <klodoma@ar-sd.net>,
	pgsql-sql@postgresql.org
References: <20040603091627.258131E338@ar-sd.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20040603091627.258131E338@ar-sd.net>
User-Agent: Mutt/1.5.6i

On Thu, Jun 03, 2004 at 12:16:43 +0300,
  Andrei Bintintan <klodoma@ar-sd.net> wrote:
> 
> 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) 

I think something like the following will work:

CREATE TABLE t1(
id serial PRIMARY KEY,
active boolean NOT NULL DEFAULT TRUE,
num int4 NOT NULL,
unique (id, active)
);
CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active;

CREATE TABLE t2(
id serial PRIMARY KEY,
active boolean NOT NULL DEFAULT TRUE,
num int4 NOT NULL,
unique (id, active)
);
CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active;

CREATE TABLE relations(
id serial PRIMARY KEY,
id_t1 int4 NOT NULL,
active_t1 boolean NOT NULL DEFAULT TRUE
  constraint t1_true check(active_t1),
id_t2 int4 NOT NULL,
active_t2 boolean NOT NULL DEFAULT TRUE
  constraint t2_true check(active_t2),
foreign key (id_t1, active_t1) references t1 (id, active),
foreign key (id_t2, active_t2) references t2 (id, active)
);