Re: Possible problems with cyclic references

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com