Advice on Cascade Delete not being enforced between tables

[kawaisunn]

I'm a newb to Access and SQL and database management. I have a main table and a dependent table with a 1 to 1 relationship, relationship integrity and cascade update and delete. I used an append query after I had modified several records, and after the append deleted one of the records from the main table. It remained in the dependent table. Access didn't catch the disparity even after a restart. I broke the link and tried to reestablish it, and of course Access wouldn't let me. I deleted the record manually from the dependent table and all was well.

Why would referential integrity/cascade delete not be recognized after an append query?

Access 2010, used Microsoft Office Help and Google to try to find a reason.

This is the second time in two days that cascade delete has been broken in two days after using a query on the main table. Yesterday, after referential integrity was broken, deleting the records from the dependent table allowed me to restore Cascade Delete functionality and it worked for the rest of the day.

 

[Anakardian]

Cascade deletes are quite dangerous.

If you only have those two tables it might not matter much but when you add the next table, things will also disappear from there or they will stop working.

I have no idea why this is occurring but I would recommend that you re-think the reason for having something cascade deleted in the first place.

 

[gemma-the-husky]

I suspect something is amiss.

if you have a true 1-1 relation, there is no parent-child relationship (logically, I think). Maybe that is why you do not get a cascading delete.

Why do you need a 1-1. They are very rare beasts.

(and I agree with anakardian about the cascading updates/deletes. I dislike the idea in principle and would never ever use it, although some do)

 

[kawaisunn]

The child table drives our web interface. In this case, it is a free public portal to abandoned mine property locatations, as well as other data such as production, water quality etc. We are a non-profit entity funded partially by the state, partially by grants, and attached to a land grant university.

The 1 to 1 seems to make sense because the property name is the primary key, and if it exists on the child but not in the parent there is a 505 error on the web side. As there has been for many of these properties for many years.

Cascade Delete is being enforced to save me some time while cleaning up this database. This DB was created in the 1980's, and there are many issues. I have spent weeks doing queries and library research on properties just to get to the point referential integrity could be enforced. The updated database is due soon as a deliverable on a grant.

I recently inherited this database and have the enviable job of pulling off about 30 years worth of band-aids, since the database is too large to consider starting over from scratch. The worst was that the primary key had become so polluted that a secondary 'key' was created to relate to all of the child tables. I don't know why this seemed better or easier than cleaning up, but it created a Gordian Knot. Now, at least, the primary key in the main table is driving.

Two days ago I deleted some properties in the primary table from a query result. The deletes did not cascade. I severed the link between the parent and child since Access wasn't recognizing that referential integrity had been broken. As it should be, the link could not be re-established. After hand deleting the entries in the child, I re-established the link and referential integrity , and Cascade Delete worked fine the rest of the day with a 1 to 1. Yesterday I had to pull some entries out of the parent table to import into ArcGIS for spatial 're-education.' I noticed a bunk property that needed removed from the parent and child, but since I assumed the day before the reason that Cascade Delete failed was because I did not delete the entries directly from the parent table, I preserved the entry until after I appended the updated data to the parent table. When I deleted the bunk property from the parent table Cascade Delete did not remove that entry from the child.

One property is no big deal to query out of the other tables, but what concerns me is I don't understand how referential integrity can be selected but not enforced. Cascade Delete is a temporary situation, but I need to trust referential integrity. I don't look forward to having to query to make sure the public web portal won't break every time I modify a table.

I am a newb to Access and SQL, but this database is now my baby. I appreciate any suggestions or criticisms. And I would appreciate living in a world where I can enforce or troubleshoot referential integrity. I have done my homework on the Microsoft Office help and Google, maybe I'm not searching with the right lingo. That's why the gurus and needy are reading this.

 

[gemma-the-husky]

I cannot recollect having used a one-to-one relation. I still think this may be the issue. given tables a and b with a one-to-one correspondence, how do you assert that

a ----> b and not
b ----> a, if you see what I mean

how have you set this up in the relation window?

 

[jdraw]

I don't think Cascade delete makes any sense when a relationship is truly 1 to 1.
I use Cascade Delete in 1 to Many. But it many instance just doing a logical delete (eg setting IsDeletedFlag = True is a better approach). Here's a couple of related excerpts.

From stackoverflow
Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, and OrderLine record is part of it's parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.

You should not use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.

From StackExchange:

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All childs of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

 

[kawaisunn]

Cascade deletes are quite dangerous.

If you only have those two tables it might not matter much but when you add the next table, things will also disappear from there or they will stop working.

I have no idea why this is occurring but I would recommend that you re-think the reason for having something cascade deleted in the first place.

Hopefully some of the information I provided above will help explain. Actually, for the task at hand, I would LOVE it if deleting any key from the main table would remove that reference from that database forever (except in my TrackChange table, of course).

 

[kawaisunn]

I cannot recollect having used a one-to-one relation. I still think this may be the issue. given tables a and b with a one-to-one correspondence, how do you assert that

a ----> b and not
b ----> a, if you see what I mean

how have you set this up in the relation window?

Gemma-the-Huskey: I do see what you mean. I set the relationship between tables lowbrow by clicking on the link and tagging check boxes. It may very well be that my related keys are linked backwards ie. from the child to the parent. That would certainly explain it if the relationship between the tables could be constructed 'backwards' in the Relationships view. I will test this and post what I find.

 

[kawaisunn]

I don't think Cascade delete makes any sense when a relationship is truly 1 to 1.
I use Cascade Delete in 1 to Many. But it many instance just doing a logical delete (eg setting IsDeletedFlag = True is a better approach). Here's a couple of related excerpts.

From stackoverflow
Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, and OrderLine record is part of it's parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.

You should not use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.

From StackExchange:

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All childs of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

jdraw: I don't understand why 1-1 may be a bad idea in the relationship of these two tables, but I am open to suggestion and willing to do research. Yeah, newb and all that. My argument for 1-1 is that in no case should there ever be a property in the child that is not in the parent, or in the parent that is not in the child, and no query should ever return any relates that are not keyed in both the parent and child. If I am misunderstanding the parameters of this relationship, please lead me to the light!

The best condition is that if I remove a bunk entry from the parent or the child, it would be removed from both tables (and any other table linked with referential integrity enforced), and no one could simply enter a new mine location without having to satisfy all of the requirements for every table (in a form, of course) needed to maintain the degree of integrity so our web interface won't break.
I'll experiment with changing the relationship to 1 to many and see if that resolves why Cascade is breaking only sometimes. I would like more information about Leave No Orphans! and how to implement that. It may be be closer to what I am trying to accomplish with cleaning up this database. I'm not afraid of hitting the books and getting some SQL under my feet, but a point in the right direction(s) would be a great help.

 

[jdraw]

If a relationship is truly 1 to 1, there is no parent child. In many instances you could reduce to a single table. I'm not saying a 1 to 1 relationship is a bad idea. I'm saying that cascade delete doesn't make sense(to me anyway) in a 1 to1 relationship.

Tell us more of the "entities" in this 1 to 1 relationship. It's possible that your issue is rooted in the table(s) design/relationships.

I think that if this statement

(except in my TrackChange table, of course).

is a real concern, then you may want to deal with logical deletes (setting a flag) rather than physical deletes(physical removal from the database).