Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6NERhf81544 for ; Mon, 23 Jul 2001 10:27:43 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15OgTG-0005hc-00; Mon, 23 Jul 2001 15:14:06 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: Jan Wieck Subject: Re: Possible problems with cyclic references Date: Mon, 23 Jul 2001 15:26:31 +0100 X-Mailer: KMail [version 1.2] Cc: pgsql-sql References: <200107231418.f6NEIRi23924@jupiter.us.greatbridge.com> In-Reply-To: <200107231418.f6NEIRi23924@jupiter.us.greatbridge.com> MIME-Version: 1.0 Message-Id: <01072315263207.27033@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/308 X-Sequence-Number: 4114 Hi Jan, Thanks for the additional info. I did, having already posted the msg, tru to create the references, but found that it would not let me do that as I was trying to create a reference to a table that didn't exist yet. I ended up setting up a one-way reference, running pg_dump to see how to set up the reference after creating the tables (it uses create triggers), and then changing/adding these lines to my create script. Your way seems much nicer. Gary On Monday 23 July 2001 3:18 pm, Jan Wieck wrote: > Gary Stainburn wrote: > > Hi all, me again. > > > > I've been looking at the doc's again (must stop doing that!) > > > > I've been looking at the 'references' clause to implement referential > > integrity. My problem is that I'm wanting to create a cyclic reference, > > and was wondering what problems this may cause, e.g. when restoring from > > a pg_dump. > > > > I have a region table (rregion character(2), rname varchar(40), rliasson > > int4). > > I have a teams table (ttid int4, tregion character(2) references > > region(rregion),...) > > I have a members table (mid int4, mteam references teams(tid),.........) > > > > Pretty straight forward so far, a member must be a part of a team and a > > team must be in a region. My problem is that I want to set rliasson as a > > reference to members (mid) as the Regional Liasson Officer for each > > region is a member. > > No problem. pg_dump outputs commands to disable referential > integrity checks during the restore. > > And you could even make rliasson NOT NULL. All you have to do > then is to have the constraints INITIALLY DEFERRED and insert > all the cyclic rows in one transaction. > > Add the constraint to the region table with ALTER TABLE after > creating the members table. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000