Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by hub.org (8.10.1/8.10.1) with ESMTP id e58HxET64350 for ; Thu, 8 Jun 2000 13:59:14 -0400 (EDT) Received: from mcadnote1 (ppm227.noc.fukui.nsk.ne.jp [210.161.188.102] (may be forged)) by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP id CAA01731; Fri, 09 Jun 2000 02:59:06 +0900 From: "Hiroshi Inoue" To: "Niall Smart" Cc: Subject: RE: Problem with subquery in CHECK constraint. Date: Fri, 9 Jun 2000 03:01:50 +0900 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <393F8A00.1D2D4AB5@ebeon.com> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200006/95 > -----Original Message----- > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On > Behalf Of Niall Smart > > > CONSTRAINT TYPE_CD_OK CHECK ( > EXISTS (SELECT 1 FROM XREF WHERE > XREF_GROUP = 'CUST_TYPE' AND > XREF_CD = TYPE_CD) > ) > > > > There seems to be more serious problems. > > 1) The constraint is not only for the defined table but also > for referenced > > tables in the subquery. > > I don't understand what you mean -- the constraint only > constrains 1 column in one table... > Doesn't the constraint mean that for any row in table CUST,there *always* exist some rows in the table XREF such that satisfies XREF_GROUP='CUST_TYPE' AND XREF_CD=TYPE_CD ? If all such rows are deleted from the table XREF,above condition isn't satisfied any longer. So isn't the constraint for the table XREF either ? Regards. Hiroshi Inoue Inoue@tpf.co.jp