Re: Are circular REFERENCES possible ?

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 wrap all the
changes that put the database into a temporary inconsistent
state into a transaction. What is a good idea and strongly
advised anyway.

DEFERRED means, that the consistency WRT the foreign key
constratins will be checked at COMMIT time instead of 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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com