Thread: two tables - foreign keys referring to each other...]
Chris, > ..or generally: how do you create two crosslinked foreign keyed tables? > > hopefully an easy problem for the real professionals! No. The problem is: Why would you want to create two crosslinked foriegn-keyed tables? As an experienced SQL professional, this seems like a recipe for instant disaster to me. The purpose of a foriegn key is to enforce a parent-->child or data table<--reference list relationships, to prevent incomplete or erroneous records from being added. This requires the foriegn key relationship to be one-way. In fact, if you built your two-way foriegn keys (using ALTER TABLE), I think you might find that you can't add any records to either table. Certainly you won't be able to delete any. I once had an experience with a complex legacy data structure where I accidentally set up a circular foriegn key relationship (among 5 tables), and I had to re-build the database from scripts to fix it. If you're looking to set up a many-to-many relationship, then what you want is a linking table. However, I highly advise you to pick up a primer on database design (such as "Database Design for Mere Mortals") before proceeding any further, or you'll end up spending the next year paying for what you don't understand now. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: two tables - foreign keys referring to each other...]
From
jdassen@cistron.nl (J.H.M. Dassen (Ray))
Date:
Josh Berkus <josh@agliodbs.com> wrote: >In fact, if you built your two-way foriegn keys (using ALTER TABLE), I >think you might find that you can't add any records to either table. >Certainly you won't be able to delete any. It can be done: use DEFERRABLE foreign key constraints (see http://www.postgresql.org/docs/aw_pgsql_book/node161.html) Ray -- Personally, I guess I'd favor a sort of modified form of medieval justice. The idea is that you throw the lawyers for both parties into a pond. The person whose lawyer sinks would be declared the winner. Rob Kleinschmidt