Re: Possible problems with cyclic references

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