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.
