From: Andrei Bintintan <klodoma@ar-sd.net>
To: pgsql-sql@postgresql.org
Subject: Reference with condition on other table column?
Date: Thu, 3 Jun 2004 12:16:43 +0300
Message-ID: <20040603091627.258131E338@ar-sd.net> (raw)
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'.
I hope I was so clear as possible.
Thnkx in advance for helping.
Andy.
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: klodoma@ar-sd.net
Subject: Re: Reference with condition on other table column?
In-Reply-To: <20040603091627.258131E338@ar-sd.net>
* 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