Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id LAA26239 for ; Fri, 25 Feb 2000 11:23:27 -0500 (EST) (envelope-from wieck@debis.com) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m12ONL6-0003kgC; Fri, 25 Feb 100 17:11 MET Message-Id: From: wieck@debis.com (Jan Wieck) Subject: Re: [SQL] Finding missing records... In-Reply-To: <4.2.2.20000225105014.00ba6bf0@mail.venux.net> from Matthew Hagerty at "Feb 25, 2000 10:56:18 am" To: Matthew Hagerty Date: Fri, 25 Feb 2000 17:11:35 +0100 (CET) CC: pgsql-sql@postgreSQL.org Reply-To: Jan Wieck X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > Greetings, > > I have two tables that maintain a 1:1 relationship via an int based primary > key. A record in the primary table should never exist without an > associated record in the secondary table, however somehow this has > happened. Can someone demonstrate a query that would show all records in > the primary table that do not have associated records in the secondary table? SELECT att_pk FROM tab_pk WHERE NOT EXISTS (SELECT att_fk FROM tab_fk WHERE att_fk = att_pk); This is the SQL3 referential definition of PENDANT. It'll not be in the FOREIGN KEY support of 7.0, but we'll discuss if we implement it for 7.1 (even if it will be damned slooooow if done on every delete/update to an FK table). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #