Re: Reference with condition on other table column?

On Thu, Jun 03, 2004 at 12:16:43 +0300,
Andrei Bintintan <klodoma(at)ar-sd(dot)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)
);