X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 679759FB1C8 for ; Mon, 24 Jul 2006 14:53:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 53245-05 for ; Mon, 24 Jul 2006 14:53:39 -0300 (ADT) X-Greylist: delayed 08:54:27.403143 by SQLgrey- Received: from furbychan.cocan.org (furbychan.cocan.org [80.68.91.176]) by postgresql.org (Postfix) with ESMTP id 5CEFF9FA4C5 for ; Mon, 24 Jul 2006 14:53:39 -0300 (ADT) Received: from rich by furbychan.cocan.org with local (Exim 3.35 #1 (Debian)) id 1G54cK-00055a-00; Mon, 24 Jul 2006 18:53:20 +0100 Date: Mon, 24 Jul 2006 18:53:20 +0100 To: Bruno Wolff III , pgsql-sql@postgresql.org Subject: Re: Referential integrity (foreign keys) across multiple tables Message-ID: <20060724175320.GA18434@furbychan.cocan.org> References: <20060722133257.GA9785@furbychan.cocan.org> <20060723183237.GA22586@wolff.to> <20060724085907.GA19981@furbychan.cocan.org> <20060724175148.GA25994@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060724175148.GA25994@wolff.to> User-Agent: Mutt/1.5.9i From: Richard Jones X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200607/300 X-Sequence-Number: 25874 On Mon, Jul 24, 2006 at 12:51:48PM -0500, Bruno Wolff III wrote: > On Mon, Jul 24, 2006 at 09:59:07 +0100, > Richard Jones wrote: > > On Sun, Jul 23, 2006 at 01:32:37PM -0500, Bruno Wolff III wrote: > > > On Sat, Jul 22, 2006 at 14:32:57 +0100, > > > Richard Jones wrote: > > > > > > > > Now I want to add a column to page_contents, say called link_name, > > > > which is going to reference the pages.url column for the particular > > > > host that this page belongs to. > > > > > > What are you trying to accomplish by this? > > > > Data integrity. > > This doesn't make sense in isolation. If that is all you are trying to do, > then you don't need to do anything to the database design as the information > is already there. The application just needs to do a join when querying the > data. I'm not sure what this means. By "data integrity" I just meant that I don't want applications to create page_contents.link_name fields which could point to non-existent URLs. (A URL consists of a particular hostid and url, since you're not allowed to have one host pointing to pages on another, and this is where the requirement for a foreign key which spans two tables comes from). Perhaps I meant "data consistency"? Anyway without some sort of check, be it a reference or a trigger -- assuming a trigger is possible -- then an application might create such a bad link. Rich. -- Richard Jones, CTO Merjis Ltd. Merjis - web marketing and technology - http://merjis.com Team Notepad - intranets and extranets for business - http://team-notepad.com