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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
