Records in Non-Related Table being deleted

[Musoguy]

I have two tables. A Department table and a Job Position Table. Elsewhere in the solution I have a TO of each table that are connected through a "departmentid" key. Through this relationship is also set to delete any related Job Positions if a department is deleted. 

I have a second TO of the Department table that is not related to any other TO. A script I am writing goes to the layout showing records from this TO, deletes all the records and imports a new set of records. However when the Delete all Records script step runs, all the related records from the Job Position table are also being deleted. For the life of me I can't work out why. It can't be to do with the other TOs I mentioned above  because they are not related to this TO. I am completely confused! Am I overlooking something simple? Again the TO in question is not related to any other TO. What am I missing? Hope someone can help.

[comment]

What you describe is the expected behavior. A table may have multiple occurrences - but they all show the same records from the same base table. A record does not have multiple occurrences. When a record is deleted, it is deleted in all occurrences of the table.

Keep in mind that the option to delete related records is a data integrity measure, designed to ensure there would be no orphans in the child table. It would fail its intrinsic purpose if it worked the way you expect.

Edited May 6, 2016 by comment

[LaRetta]

Hi,

That is not normal behavior.  Can you post your script here please?

[comment]

15 minutes ago, LaRetta said:

That is not normal behavior.

I have just checked this on my own, and it is the normal behavior. I did not use any scripts.

Edited May 6, 2016 by comment

[LaRetta]

I never use cascading deletes but I had read by other developers that they created a specific TOG for cascading deletes so it would not delete child records in their primary TOGs.  I tested via script and you are correct ... it deletes them regardless.  And that is why one should never believe what one reads without testing it ourselves. 

[comment]

27 minutes ago, LaRetta said:

I had read by other developers that they created a specific TOG for cascading deletes so it would not delete child records in their primary TOGs

Hm... Perhaps this could work if the cascading deletes TOG was in another file?

[Musoguy]

Thanks for the replies guys. I understand that if I delete a record from the parent table that it will be deleted from the database whichever parent TO the layout is based on. But just to make sure I understand correctly, are you saying that if I define a relationship between a parent and child TO where the child records are deleted if the parent records are, then the child records will be deleted even if I delete the parent record from a layout showing records from a different TO of the parent table? That may have been confusing! Let me try again:

2 Tables: Parent Table (2 table occurrences) , Child Table (1 table occurrence)

ParentTO1 ---> ChildTO1 (Related Child records deleted if parent record is deleted)

ParentTO2 

The layout is based on ParentTO2. So if I delete a record from this layout, then the related Child Table records are also removed?

I should add, this solution is being created in Filemaker 11, as that is what the client uses if that makes a difference. Not sure it does in this instance,

Edited May 6, 2016 by Musoguy

[comment]

Just now, Musoguy said:

But just to make sure I understand correctly, are you saying that if I define a relationship between a parent and child TO where the child records are deleted if the parent records are, then the child records will be deleted even if I delete the parent record from a layout showing records from a different TO of the parent table?

Yes, that is exactly what I am saying.

[Musoguy]

Huh! Good to know. Thanks

40 minutes ago, LaRetta said:

I never use cascading deletes but I had read by other developers that they created a specific TOG for cascading deletes so it would not delete child records in their primary TOGs.  I tested via script and you are correct ... it deletes them regardless.  And that is why one should never believe what one reads without testing it ourselves. 

And LaRetta, I have done exactly that in the past. And you're absolutely right, never believe what you read! Now I know! Thanks for the help both of you.

[LaRetta]

I just tested and using another file for the child table makes no difference since all exists within the current file's graph.  Child records still get deleted.  This may have been an early bug (NOT deleting child records) that was later fixed.  But this is why I've just never used cascading deletes ... I've read so many horror stories over the years that it simply reaffirms my belief that it is better to handle them by developer script.

[Musoguy]

So you never check that box when creating a relationship between a parent and child table, you always deal with it via a script? Interesting

[LaRetta]

Well I try never to say never  and in a simple solution I might be tempted but even now, thinking about it, I break into a cold sweat because simple solutions never stay simple.  I would rather have orphans than risk it.  And, down the road a few years from now, you or another developer may not remember and that is when the 'all my records mysteriously disappear' begins to happen.

I would like to hear Michael's thoughts on it.

 

[bruceR]

It is hard to imagine a scenario where you would want to do this. (Delete parent, NOT delete related child record)

Your import process into parent could do import-replace existing, thereby updating the parent records.

But note that you can still maintain the delete-related relationship and selectively choose not to delete the child records in the following way.

Find desired set of parent records which are candidates for deletion.

Perform a replace operation on the parent field that links to the child records, emptying that field.

At this point the records are no longer related to the child records. Now do your delete. 

[Fitch]

Some developers create a specific TOG for cascading deletes not because they don't know how the feature works, but as a reference tool. It's easier to keep track of them when they're all in one place.

As to whether you should enforce referential integrity, the answer is probably yes.

[comment]

1 hour ago, LaRetta said:

I would rather have orphans than risk it.

Well, if we were doing this by the book (in this case, the database design book), then once the database architect has indicated "one" or "exactly one" next to the parent entity on the ERD, the database implementer is obliged to enforce it and turn on automatic deletion of children.

Since most often the Filemaker developer is wearing both hats, the question is really one of business rules. Keep in mind that the risk of having orphans where they are not desired (and could potentially screw up totals, for example) is a real risk, too.

 

1 hour ago, LaRetta said:

I just tested and using another file for the child table makes no difference

That's not quite what I meant. I was speculating about having the entire TOG (parent and child, related by an auto-deleting relationship) in another file, with no tables on its own. Not that I would recommend this as a practice anyway.

 

Edited May 6, 2016 by comment

[Fitch]

Let's say you're using the separation model, with a single data file and a single UI file. Using the Delete related records (DRR) option:

1. With DRR on in the data file, child records are always deleted.

2. With DRR off in the data file, but on in the UI file:

a. working in the UI file, children are deleted;

b. working in the data file, and the UI file is open, children are deleted;

c. working in the data file, and the UI file is closed, children are NOT deleted;

The moral of this story is, enforce data integrity constraints in the data file.

[comment]

53 minutes ago, Fitch said:

Let's say you're using the separation model, with a single data file and a single UI file.

That is a good example - esp. the difference between 2b and 2c.