Thread: Reference with condition on other table column?

On Thu, 3 Jun 2004, Andrei Bintintan wrote:

> 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'.

Hmm, I can see is having two other tables that you reference that have
rows containing num added/removed by triggers when T1 or T2 are changed,
so that inserting an active='y' row inserts a row into the appropriate
one, update a ='n' -> 'y' inserts a row, update 'y'->'n' removes a row and
deleting a ='y' row removes a row.  That might get messy though.

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)
);