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--