From klodoma@ar-sd.net Thu Jun 3 09:16:35 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F2336D1BAF5 for ; Thu, 3 Jun 2004 06:16:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58585-03 for ; Thu, 3 Jun 2004 09:16:36 +0000 (GMT) Received: from ar-sd.net (unknown [81.196.32.112]) by svr1.postgresql.org (Postfix) with ESMTP id F3322D1BA0A for ; Thu, 3 Jun 2004 06:16:31 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 48C8621703 for ; Thu, 3 Jun 2004 12:16:27 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with ESMTP id 258131E338 for ; Thu, 3 Jun 2004 12:16:27 +0300 (EEST) From: "Andrei Bintintan" To: Subject: Reference with condition on other table column? Date: Thu, 3 Jun 2004 12:16:43 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0030_01C44964.A2004360" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcRJS3snOwKmY1ylSgi+Ug7A2DEPZQ== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2739.300 Message-Id: <20040603091627.258131E338@ar-sd.net> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/19 X-Sequence-Number: 17964 This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C44964.A2004360 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_0030_01C44964.A2004360 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi to all= ,=20

I have th= e following=20 tables:

CREATE TABLE t1(
id serial= PRIMARY KEY,
active boolean NOT NULL DEFAULT 'y',n= um int4 NOT NULL,
);
CREATE=20 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=20 NULL,
);
CREATE=20 UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE=20 active;

CREATE=20 TABLE relations(
id serial = PRIMARY=20 KEY,
id_t1 int4 NOT NULL REFERENCES=20 t1(num),
id_t2 int4 NOT = NULL=20 REFERENCES t2(num)
);

On tables=20 T1 and T2 the "num" columns have unique values for all lines that= have=20 active=3D'y'(true).

How can I write a constraint = on Table=20 T1 and Table T2 that if the "num" from T1 and "num" from T2 are= =20 referenced from table "relation" than I cannot update the "active" fie= ld to=20 "false". My target is that I don't want to have any reference from "relatio= n"=20 table to T1 and T2 where in the T1 and T2 the active field is "n"(false)=20

or with other words:

if a line from T1/T2 is referenced from= =20 table "relations" than I don't want to be able to put=20 active=3D'y'.

I hope I was so clear as poss= ible.=20

Thnkx in=20 advance for helping.

Andy.

=

------=_NextPart_000_0030_01C44964.A2004360-- From sszabo@megazone.bigpanda.com Thu Jun 3 14:23:59 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CFBE2D1B193 for ; Thu, 3 Jun 2004 11:23:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58810-03 for ; Thu, 3 Jun 2004 14:24:03 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 57E55D1B191 for ; Thu, 3 Jun 2004 11:23:58 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 7746935262; Thu, 3 Jun 2004 07:24:02 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 75C2D350DA; Thu, 3 Jun 2004 07:24:02 -0700 (PDT) Date: Thu, 3 Jun 2004 07:24:02 -0700 (PDT) From: Stephan Szabo To: Andrei Bintintan Cc: pgsql-sql@postgresql.org Subject: Re: Reference with condition on other table column? In-Reply-To: <20040603091627.258131E338@ar-sd.net> Message-ID: <20040603071816.Y83422@megazone.bigpanda.com> References: <20040603091627.258131E338@ar-sd.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/22 X-Sequence-Number: 17967 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. From bruno@wolff.to Thu Jun 3 18:14:52 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ACC23D1C4ED for ; Thu, 3 Jun 2004 15:14:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38694-03 for ; Thu, 3 Jun 2004 18:14:53 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id A767BD1BAAD for ; Thu, 3 Jun 2004 15:14:47 -0300 (ADT) Received: (qmail 8130 invoked by uid 500); 3 Jun 2004 18:20:25 -0000 Date: Thu, 3 Jun 2004 13:20:25 -0500 From: Bruno Wolff III To: Andrei Bintintan Cc: pgsql-sql@postgresql.org Subject: Re: Reference with condition on other table column? Message-ID: <20040603182025.GB5022@wolff.to> Mail-Followup-To: Andrei Bintintan , pgsql-sql@postgresql.org References: <20040603091627.258131E338@ar-sd.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20040603091627.258131E338@ar-sd.net> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/25 X-Sequence-Number: 17970 On Thu, Jun 03, 2004 at 12:16:43 +0300, Andrei Bintintan 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) );