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