Received: from openmail.kick.com (adsl-63-205-173-34.dsl.snfc21.pacbell.net [63.205.173.34]) by hub.org (8.10.1/8.10.1) with ESMTP id e58IFhT00873 for ; Thu, 8 Jun 2000 14:15:51 -0400 (EDT) Received: from planetblargh (adsl-63-205-173-36.dsl.snfc21.pacbell.net [63.205.173.36]) by openmail.kick.com (8.9.3/8.9.3) with SMTP id LAA09780; Thu, 8 Jun 2000 11:15:30 -0700 Message-ID: <004d01bfd175$7d9af7b0$0c64010a@kick.com> From: "Stephan Szabo" To: "Niall Smart" Cc: References: <000901bfd0df$f57c1f20$2801007e@tpf.co.jp> <393F8A00.1D2D4AB5@ebeon.com> Subject: Re: Problem with subquery in CHECK constraint. Date: Thu, 8 Jun 2000 11:15:08 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200006/96 > 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... Think of this sequence: insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '1'); insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '2'); insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '3'); insert into cust (cust_id, name, type_cd) values (1, 'a', '1'); insert into cust (cust_id, name, type_cd) values (2, 'b', '2'); delete from xref where xref_cd='1'; I believe that technically the delete should fail because it breaks the type_cd_ok constraint, but right now, we wouldn't be even checking that constraint to notice that we're breaking it.