Re: Are circular REFERENCES possible ? - Mailing list pgsql-sql

From Stephan Szabo
Subject Re: Are circular REFERENCES possible ?
Date
Msg-id Pine.BSF.4.21.0108070918100.34060-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: Are circular REFERENCES possible ?  ("Josh Berkus" <josh@agliodbs.com>)
List pgsql-sql
On Tue, 7 Aug 2001, Josh Berkus wrote:

> Denis,
> 
> > I have a case where I wanted to do circular REFERENCES, is this
> > impossible ?
> 
> It can be done.  It's just a bad idea.
> 
> > 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.
> 
> You can do this by applying the constraints *after* table creation.
> However, you will forever fight the following problems:
> 
> 1. You will not be able to add any records to Customers without dropping
> and re-creating the REFERENCES each time.
> 2. You will never be able to delete a record from either table due to
> the circular reference check.
> 3. Some UPDATES will also fail for the same reason.

This is actually not quite true.  You need to make the references in
a circular relationship deferrable andprobably initially deferred and then  
add pairs if necessary within one transaction (note: there are some bugs
in deferred constraints if you do somewhat wierd things)

The other tricks are things like for deletes, you may want to use
on delete set null for the the default shop on deliveries (ie, if the
shop they use is deleted, they don't have a default shop until
someone gives them one).

However, I agree that generally circular constraints are painful and its
often better to think of another way to hold the relationship.



pgsql-sql by date:

Previous
From: "Josh Berkus"
Date:
Subject: Re: Re: Adding an INTERVAL to a variable
Next
From: "Josh Berkus"
Date:
Subject: Re: Why can't I .........