Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D6913475D00 for ; Wed, 29 Jan 2003 04:09:55 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 43DC64776FE for ; Wed, 29 Jan 2003 03:15:51 -0500 (EST) Received: (qmail 19777 invoked by uid 500); 29 Jan 2003 08:28:38 -0000 Date: Wed, 29 Jan 2003 02:28:38 -0600 From: Bruno Wolff III To: Rodger Donaldson Cc: pgsql-sql@postgresql.org Subject: Re: Cross-table constraints Message-ID: <20030129082838.GA19737@wolff.to> Mail-Followup-To: Rodger Donaldson , pgsql-sql@postgresql.org References: <20030128070514.GB7146@diaspora.gen.nz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030128070514.GB7146@diaspora.gen.nz> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/476 X-Sequence-Number: 11341 On Tue, Jan 28, 2003 at 20:05:15 +1300, Rodger Donaldson wrote: > > The problem: I have two tables, add_queue and sites, on a postgresql > 7.2.x database. add_queue is where items go to be reviewed by a human > before being moved into sites. One of the things I'd like to do is to > cut down on the amount of work done by humans filtering out dupes and > sundry other problems. Create a third table with three id columns. The first is the primary key and the other two tables should reference it. The second and third should allow for nulls and each reference the id field in one of the other two tables. You also need a table constraint that forces exactly one of these two fields to be null. And two more constraints to make sure they match the primary key when they aren't null. This should make sure each id is in exactly one of the two original tables and that any id in the two original tables is in the new table.