fun with referential integrity

[Ulf Wiger]

I decided to actually use my rdbms contrib today. (:

I had a number of tables that I wanted to connect in a
multi-level pattern of cascading delete. Due to some bad
design decisions previously, I ended up having to define a
cyclical pattern of dependencies, but pressed on with
unwarranted confidence.

(Just to clarify, what I was aiming for was this: if one
deletes an object A, then all B and C should also be deleted
if A.id == B.a_id, and B.id == C.b_id. The cyclical
dependency was that one should also delete B (where B.id ==
C.b_id) anytime one deletes C (don't ask).)

I discovered that rdbms did not, in fact cascade down more
than one level. That is, when deleting A, all related B were
deleted, but the Cs remained. I went into rdbms and made the
obvious fix, which immediately threw me into an endless loop
-- not good.

After a few hours, I realised that the solution for
cascading delete was obvious: delete A first, then do the
cascade, and the same thing downwards. This worked, too.

The real problem is of course with cascading update, which
suffers from the same issues, but requires a different
solution. I have to maintain a visited list, I guess, if I
am to allow for the possibility of cyclical dependencies. Of
course the visited list must support the notion of nested
transactions, which means several levels of visited lists.

... or should I make it easier on myself and simply disallow
cyclical dependencies? This seems like a fairly limiting
restriction. How do the real pros do it? Does anyone know?

/Uffe
-- 
Ulf Wiger, Senior Specialist,
   / / /   Architecture & Design of Carrier-Class Software
  / / /    Strategic Product & System Management
 / / /     Ericsson AB, Connectivity and Control Nodes