Hello,

are there any complications in general with a many-to-many "join
table" having cascading deletes on both foreign keys to the
interconnected tables? After all, this means that the "link entities"
get deleted whoever comes first... could this pose a problem?

I cannot think of any concrete examples where this could really do
harm, but if there are, please enlighten me.

Karsten

The ANSI/ISO model is that first you mark all the rows in all the
tables with a delete flag. If no constraints are violated, then a
second pass does the actual removal. If a constaint is violated at any
point, then the engine does a rollback and sends an exception.

The multiple passes can be expensive, but they are safe.

On 19 jun, 22:04, Karsten Wutzke <kwut... (AT) web (DOT) de> wrote:
Sorry, fail to see how this has anything to do with database theory.

The concept of "cascade delete" is pretty well understood and pretty
well-defined, and if some DBMS chooses to support it, it is that
DBMS's responsibility to do so properly. That is, it is the
responsibility of that DBMS's authors to see to it that their
implementation does not cause any "problems". If it did, their
implementation would be flawed.

On 20 jun, 15:35, -CELKO- <jcelko... (AT) earthlink (DOT) net> wrote:
Is the SQL standards crew now also prescribing how implementations
should be done ?

Erwin wrote:
It's not unreasonable to describe a transitive behaviour like this using an
algorithm as example, i.e, must behave like this algorithm behaves. I doubt
they dictate the actual implementation...

Erwin wrote:
SQL Server 2000 was flawed, but in a responsible way, if I may say that.

In other words, they detect when adding a new FK can yield multiple
cascade paths over the records of any table at the time the FK is
declared, and reject that constraint if there *might* be a problem
at runtime.

Obviously that's not as good as detecting when there actually *is*
a record that has multiple paths and only doing the operation once,
but at least it's better than just blowing up at update time.

Some of these cycle problems are known to be ambigous or NP-complete.
Add in deferred constraints and it is a real mess. The safety rule is
to keep the cascades paths as trees -- no cycles.

On 23 jun, 16:38, -CELKO- <jcelko... (AT) earthlink (DOT) net> wrote:
Sorry, but I'm mystified.

Where did the OP mention "cycle problems" ?

The only thing I had the impression the OP was talking of was a
junction table J between R1 and R2, with a cascade delete rule from R1
to J, as well as from R2 to J.

You seem to have gotten a different impression.

And then, second.

While of course I recognise that cycles hold the danger of infinite
recursion when processing them, isn't your assertion too strong ?

Personally, I would say that it is sufficient to keep the cascades
paths as DAGs. No ?

SQL Server regards two cascades to the same referenced table as a
cycle, so you cannot have, say, two employees playing different roles
in a referencing table (the company bowling team cannot have a captain
and co-captain employee ids referencing the Personnel table, even if
they are different.

A cascades to B and C
B cascades to C
I fire off a change to A, which also causes B to fire.
Does C get the change made by A or the change made by B?

It is not just possible infinite recursion, and products vary in what
they can handle. In an early version of DB2, the answer to the above
problem was a non-deterministic "whoever got there last" but now they
check for that kind of thing. A simple tree is safe in all producrts
as far as I know.