pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Possible problems with cyclic references
3+ messages / 2 participants
[nested] [flat]

* Possible problems with cyclic references
@ 2001-07-23 11:30 Gary Stainburn <gary.stainburn@ringways.co.uk>
  2001-07-23 14:18 ` Re: Possible problems with cyclic references Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Gary Stainburn @ 2001-07-23 11:30 UTC (permalink / raw)
  To: pgsql-sql

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.
-- 
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     



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Possible problems with cyclic references
  2001-07-23 11:30 Possible problems with cyclic references Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-07-23 14:18 ` Jan Wieck <JanWieck@Yahoo.com>
  2001-07-23 14:26   ` Re: Possible problems with cyclic references Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Jan Wieck @ 2001-07-23 14:18 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Possible problems with cyclic references
  2001-07-23 11:30 Possible problems with cyclic references Gary Stainburn <gary.stainburn@ringways.co.uk>
  2001-07-23 14:18 ` Re: Possible problems with cyclic references Jan Wieck <JanWieck@Yahoo.com>
@ 2001-07-23 14:26   ` Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Gary Stainburn @ 2001-07-23 14:26 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-sql

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     



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-23 14:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-23 11:30 Possible problems with cyclic references Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-23 14:18 ` Jan Wieck <JanWieck@Yahoo.com>
2001-07-23 14:26   ` Gary Stainburn <gary.stainburn@ringways.co.uk>

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