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?
Date: Thu, 3 Jun 2004 13:20:25 -0500
Message-ID: <20040603182025.GB5022@wolff.to> (raw)
In-Reply-To: <20040603091627.258131E338@ar-sd.net>
References: <20040603091627.258131E338@ar-sd.net>
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)
);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, klodoma@ar-sd.net
Subject: Re: Reference with condition on other table column?
In-Reply-To: <20040603182025.GB5022@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox