pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Denis Bucher <dbucher@niftycom.com>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Are circular REFERENCES possible ?
Date: Tue, 7 Aug 2001 16:38:28 +0100
Message-ID: <01080716382807.22099@gary.ringways.co.uk> (raw)
In-Reply-To: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com>
References: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com>

Hi Denis,

I've just had a similar experience with a 3-way circle.  I have members, who 
belong in regions.  Each region had a Regional Liasson Officer who was a 
member.

I got round it by creating the three tables, but missing  out one of the 
references - i.e. the one that links table 1 to table 3 which doesn't exist 
yet.

I then used pg_dump to see how that would re-create the tables.  It didn't 
create any references/foreign keys etc. when it created the tables, but right 
at the end, aftter the 'copy's and index creations it did a load of CREATE 
CONSTRACT TRIGGER entries.  I edited these to generate the ones that were 
missing.

This was a bit messy, but it meant that I could keep the logic of my data.

As stated in some of the other posts, you will have problems updating your 
data, with inserts.  One thing to remember here is that references aren't 
checked if the reference value is NULL.  So, you could add a customer with 
the default shop as NULL, then add a shop, and then update the customer.

I haven't checked this, but I seam to remember reading that if you do it all 
inside a transaction, the references aren't checked until the transaction is 
comitted, so you could do something like:

begin
insert customer
insert shop
comit

Gary

On Tuesday 07 August 2001 10:54 am, Denis Bucher wrote:
> Hello !
>
> I have a case where I wanted to do circular REFERENCES, is this
> impossible ?
>
> Just an example where it would be useful :
>
> We deliver to the *shops* of our *customers*.
> We have therefore two tables :
>   - customers (enterprise, financial information, and so on...)
>   - shop (with a name, street, phone number, name of manager)
>
> Now, each shop REFERENCES a customer so that we know
> to which customer belongs a shop.
>
> AND, each customer has a DEFAULT shop for deliveries, i.e. most
> customers only have one shop, or a main shop and many small ones.
> Therefore a customer should REFERENCES the 'main' or 'default' shop.
>
> Which leads to :
>
> CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES
> customers, .......)
> CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer
> REFERENCES shops, .......)
>
> But this doesn't work ! Postgres complains like "ERROR:  Relation
> 'customers' does not exist"
> when creating 'shops'.
>
> Someone told me I should create a third table, ok, but in this case I loose
> the total
> control about my logic... Do you have a suggestion ?
>
> Thanks a lot in advance !
>
> Denis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
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 (15+ messages)  latest in thread

Message-ID: <01080716382807.22099@gary.ringways.co.uk>
Permalink:  ../01080716382807.22099@gary.ringways.co.uk/
Also on:    postgresql.org/message-id/01080716382807.22099@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, dbucher@niftycom.com
  Subject: Re: Are circular REFERENCES possible ?
  In-Reply-To: <01080716382807.22099@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