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

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.
   I  don't  see why it is a bad idea to apply the full business   model to the database schema.

> > 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.
   All of this is wrong. If the constraints are  defined  to  be   INITIALLY  DEFERRED,  all  you  have to do is to
wrapall the   changes that put the database into a  temporary  inconsistent   state  into  a  transaction. What is a
goodidea and strongly   advised anyway.
 
   DEFERRED means, that the  consistency  WRT  the  foreign  key   constratins  will  be  checked  at COMMIT time
insteadof the   actual statement. So if you
 
       BEGIN TRANSACTION;       INSERT INTO customer ...       INSERT INTO shop ...       COMMIT TRANSACTION;
   It'll get you out of the circular  problem  without  dropping   and re-creating the constraints.
   The  same  applies to updates and deletes generally. Well, if   you want to you can specify ON UPDATE CASCADE and
ON DELETE   CASCADE,  so  if you delete a shop, the customers referencing   it will get deleted  automatically  too,
which might  cause   other shops referencing them ...
 

> All of this makes circular references a bad idea; references are meant
> to be heirarchical.  Heck, I got into a real mess when I accidentally
> set up a circular reference among 5 tables ... took me forever to figure
> out why INSERTS kept failing.
   Josh, maybe you should buy a newer SQL-bo... :-)
   Got ya (LOL)!
   The point is that we based our implementation of foreign keys   on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.


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