pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Possible problems with cyclic references
Date: Mon, 23 Jul 2001 15:26:31 +0100
Message-ID: <01072315263207.27033@gary.ringways.co.uk> (raw)
In-Reply-To: <200107231418.f6NEIRi23924@jupiter.us.greatbridge.com>
References: <200107231418.f6NEIRi23924@jupiter.us.greatbridge.com>

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     



view thread (3+ messages)

Message-ID: <01072315263207.27033@gary.ringways.co.uk>
Permalink:  ../01072315263207.27033@gary.ringways.co.uk/
Also on:    postgresql.org/message-id/01072315263207.27033@gary.ringways.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gary.stainburn@ringways.co.uk, JanWieck@Yahoo.com
  Subject: Re: Possible problems with cyclic references
  In-Reply-To: <01072315263207.27033@gary.ringways.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox