Re: Are circular REFERENCES possible ?

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