Reference with condition on other table column?

From: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Reference with condition on other table column?
Date: 2004-06-03 09:16:43
Message-ID: 20040603091627.258131E338@ar-sd.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stef 2004-06-03 12:12:27 Re: Date format problems
Previous Message Richard Huxton 2004-06-03 07:19:07 Re: bytea or blobs?