pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReference with condition on other table column?
3+ messages / 3 participants
[nested] [flat]
* Reference with condition on other table column?
@ 2004-06-03 09:16 Andrei Bintintan <klodoma@ar-sd.net>
2004-06-03 14:24 ` Re: Reference with condition on other table column? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-03 18:20 ` Re: Reference with condition on other table column? Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 3+ messages in thread
From: Andrei Bintintan @ 2004-06-03 09:16 UTC (permalink / raw)
To: pgsql-sql
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'.
I hope I was so clear as possible.
Thnkx in advance for helping.
Andy.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reference with condition on other table column?
2004-06-03 09:16 Reference with condition on other table column? Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-06-03 14:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-06-03 14:24 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reference with condition on other table column?
2004-06-03 09:16 Reference with condition on other table column? Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-06-03 18:20 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2004-06-03 18:20 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
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)
);
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-03 18:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-03 09:16 Reference with condition on other table column? Andrei Bintintan <klodoma@ar-sd.net>
2004-06-03 14:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-03 18:20 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox