Re: Are circular REFERENCES possible ?

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.