Обсуждение: Reference with condition on other table column?
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.
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) );