Cascade Delete and N:N Join Tables

[David W. Fenton]

I'm revising the schema for an app I've just taken over and most of
the N:N tables don't have unique indexes defined on the pair of
foreign keys (so they have lots of duplicate records), but the FK
relationships are all set as CASCADE DELETE (and, unnecessarily,
CASCADE UPDATE, since all the FKs values are from Autonumber fields,
which can't be updated, of course).

Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
because I'm never quite sure if the way *I* see the hierarchy is the
way Jet sees it. That is, I may think that something is the parent,
but Jet sometimes sees it as the child (e.g., lookup tables seem
subordinate to the main table, but they are actually the parent
tables if you define relationships for them, which I very often
don't, as a matter of fact).

Obviously, if one of the two parents of the N:N record is deleted,
then the join record is invalidated, but should I prevent that?

Or is the answer "it depends"?

In the present case, I'm included to cascade the deletions because
in the tables I'm working with, it would be OK to lose the record.

On the other hand, neither of the parents should be deleted, but if
they are, it would be easier to have the cleanup at the db engine
level.

I haven't yet decided if I want to allow these parents to be deleted
if they have children. In the past, the app has certainly been used
in that way (and that has resulted in a lot of orphan records
because RI was not enforced), but I'm not certain it should be in
the future.

Thoughts?

 

[Tom van Stiphout]

On 3 Feb 2010 03:31:23 GMT, "David W. Fenton"

Hi David,
I was following you until you said "the way I see it". There really is
only one way to see a 1:M relationship with cascade delete: if the
parent is deletes the children will be deleted as well.

Therefore, in an M:M, records in the junction table will be deleted if
a parent record (of either side) is deleted.

Whether you should allow this or not depends on the business rules.
There is no generic answer. Personally I use cascade delete very
sparingly; often I will force the user to first manually delete the
children before they can delete the parent. That way the user can see
the impact of the decision to delete the parent. Again, it depends on
the business case.

-Tom.

 

[John W. Vinson]

Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
because I'm never quite sure if the way *I* see the hierarchy is the
way Jet sees it. That is, I may think that something is the parent,
but Jet sometimes sees it as the child (e.g., lookup tables seem
subordinate to the main table, but they are actually the parent
tables if you define relationships for them, which I very often
don't, as a matter of fact).

Erm?

A Lookup table to main table relationship is always a One to Many (each lookup
value can be in many maintable values). I'd NEVER use Cascade Deletes in a
lookup table; that would delete "real data" (possibly lots of it) if you
deleted a lookup value that you mistakenly thought was obsolete.

The cascade always goes from One to Many. Never the other way!

Whether the *significance* in the real world is subordinate or preeminent is
irrelevant (he said polysyllabically).

 

[David W. Fenton]

On 3 Feb 2010 03:31:23 GMT, "David W. Fenton"

I was following you until you said "the way I see it". There
really is only one way to see a 1:M relationship with cascade
delete: if the parent is deletes the children will be deleted as
well.

Therefore, in an M:M, records in the junction table will be
deleted if a parent record (of either side) is deleted.

I'm not sure why you thought I wasn't clear on that.

But I'm very careful about the parent/child hierarchy because of my
Jet replication experience, where it matters a great deal (certain
kinds of legal relationships, such as a self-join to a required
field where the default is to set the FK field to the record's own
PK, can never work in a replicated scenario because INSERTs are done
before UPDATES, so the record can't be inserted because it's FK
value does not yet exist in the PK index), and it's not always
intuitive the way things actually work.

I once had a circular relationship that caused no problems most of
the time but occasionally ended up in a deadlock, and it was because
I was viewing the relationships upside-down in comparison to the way
the database engine saw them. The db engine was right, of course,
but it was still counterintuitive, and that's why I don't assume
that my assumptions about how things should work are correct.

Whether you should allow this or not depends on the business
rules.

I'm probably changing the business rules, as there's a lot of
orphaned data, mostly because the app was not built to deal with the
issue of having sets of data that applied only to each year (when a
new year arrived, they'd delete a bunch of child data that no longer
applied, thus invalidating the old data, which was left in the
database), but I'm also wary of implementing prevention of deletes
since the users may complain about being unable to delete records.

I think I'm going to implement no CASCADE DELETE and then explain to
them that I've tightened the data rules and if they need to delete
parent records, then I'll have to build a UI to do it for them (and
I may or may not actually delete the data -- using a delete flag,
instead).

There is no generic answer. Personally I use cascade delete very
sparingly; often I will force the user to first manually delete
the children before they can delete the parent. That way the user
can see the impact of the decision to delete the parent. Again, it
depends on the business case.

I generally don't use CASCADE DELETES much, except on something like
an invoice, where if deleting the invoice header is allowed, the
invoice items have no meaning and should be deleted along with the
header. On the other hand, I never cascade deletes from Customer to
Invoice, for obvious reasons.

In this case, it's not quite so dire if the top-level record is
deleted, and there might very well be good reasons to delete them.
I'll have to work with the app to determine whether or not I want to
allow them to delete the parent records or not, and if so, under
what circumstances. There's some child data that is not important,
and other child data that shouldn't be deleted, so I may cascade
some of the deletes and not others.

In general, though, I agree with the default position of *NOT*
cascading deletes, and only doing so when it's pretty clearly
sensible.

 

[David W. Fenton]

Erm?

A Lookup table to main table relationship is always a One to Many
(each lookup value can be in many maintable values). I'd NEVER use
Cascade Deletes in a lookup table;

Nor do I, and I didn't say I did, or intend to imply that anyone
should...

that would delete "real data" (possibly lots of it) if you
deleted a lookup value that you mistakenly thought was obsolete.

The cascade always goes from One to Many. Never the other way!

I know this. But things are not always as clean and simple as they
may seem intuitively. That's something that working with replication
taught me, that there's more going on than one may think.