How to delete orphaned records in a linked table?

[WendyWelton]

I have three tables (and I'm still on DBF)
b_tasks
b_task_2_file
b_files

The middle one is a many-to-many - associates file records with task records.

b_tasks has a primary key of task_id, that's auto-increment

My dilemma is that if we delete a task that already had an associated record in b_task_2_file, when we do a new task it uses that next number in line via the auto-increment, which then associates it with any leftover records in b_task_2_file. Basically, deleting a record in b_tasks is leaving orphans in b_task_2_file. I know I once saw a way to deal with that, but I can't remember even what kind of thing it was. Can somebody point me in the right direction? I assume this has to be a Callback of some sort?

ophan_being_reused.png

b_tasks.png

task-2-file.png

[Peter.Greulich]

Re: How to delete orphaned records in a linked table?

I'm wondering how this happens, doesn't your next new b_task get the next autoincrement #?

e.g. if you have items
1
2
3
4
5

and you delete 3, you get
1
2
4
5

now if you add a new btask you should get 6.

What am I missing?

[WendyWelton]

Re: How to delete orphaned records in a linked table?

In your example, if I delete 3, the next record would indeed still be 6. But if I delete 5, the next record is then 5. That's when we run into trouble.

[Peter.Greulich]

Re: How to delete orphaned records in a linked table?

Forgot about that scenario. Typically you would apply referential integrity in a set or in sql. You can code it of course, but... Otherwise can you use a unique key to link on that's not autoinc?

[Clipper87]

Re: How to delete orphaned records in a linked table?

Depending on the integrity you want to have of your data you could create a separate table in which you keep system/application wide preferences. One of the fields would be the last used ID for a given table. Instead of using an auto increment number in your table you'd then be using a calculated field with a user defined function that simply increments the systempreferences table field and then returns that value.

[WendyWelton]

Re: How to delete orphaned records in a linked table?

OK - so I'm hearing that there isn't some simple clear way to have it reach through and delete those records in the secondary table. I'm either going to

1 - disallow delete, and make a field called Delete instead - then filter all displays for Delete <> .t. or
2 - something else clever that I haven't thought of yet (and probably won't - 'cause I kind like my fake delete idea)

Using a UUID type PK would work also, as Frank seems to suggest - I'm using those in several places and do know how to code that. I just want simple sequential numbering for my Task records.

[The Mage]

Re: How to delete orphaned records in a linked table?

I have an auto increment in MySQL, and no matter what, if I delete 5, (the highest number in the list,) it will still make the next number 6. I was doing some testing that involved repeatedly inserting data, and then deleting it. My table contained no records the last time I did this, and the first number it entered into the record was over 140. So at least MySQL keeps track of the numbers used.

Also, while I have not set up anything like this, you should be able to set rules in your database to automatically delete child records when a parent is deleted to prevent orphan records. I haven't set up anything like this in my database, so I don't know how to go about doing this, but I know it can be done. Involves using foreign keys. (Maybe a bad idea, but I have no foreign keys in my database.)

[Rich Hartnett]

Re: How to delete orphaned records in a linked table?

Wendy I am running into the exact same problem in my police records management application. The user deletes the parent record, but not the child records. Next user creates a new parent record, and all the previous child records get linked to it, because they are sharing the same autoincrement linkingkey.

Did you ever figure out a way to have one delete button or checkbox delete the parent record and all the child records?

I know this can be done easily on the desktop side, but I don't see a way to do it on the web.

[frankbicknell]

Re: How to delete orphaned records in a linked table?

I don't use dbfs but a way to do it would be put the code to delete the child records before the parent record is deleted in the CanDeleteRecord event of the parent grid that contains the record you want to delete. The mySql code would be something like delete from ChildTable where ChildTable.LinkingID = ParentTable.LinkingID have some code that returns success then go ahead and delete the parent record.

In the old VFP days you could set the table to have referential integrity that would cascade the deletes and the updates to the child records but that required a database container i don't think you have that with alpha.

With MySql I just set up a forein key and have the deletes cascaded in the child table. MySql handles all the work.

[Rich Hartnett]

Re: How to delete orphaned records in a linked table?

Thanks Frank. I believe Alpha does in fact have referential integrity, but it is only honored on the desktop side, whereas all my applications are web based.

Maybe Wendy has found a solution.

-- Rich

[MarionT]

Re: How to delete orphaned records in a linked table?

With MySql I just set up a forein key and have the deletes cascaded in the child table. MySql handles all the work.

this is true in Microsoft SQL too

[Al Buchholz]

Re: How to delete orphaned records in a linked table?

Rich

Actually allowing a delete with an autoincrement id will cause issues of the record with the largest id is deleted.

Alternative is to mark as deleted and filtered accordingly when displaying. ( show that it is deleted and allow an undelete.)

or use an alternate custom id system that keeps incrementing from a separate indicator.

If the parent is truely gone, create an inverted set and the orphans won't link to a parent and can be deleted by select where the parent id field is blank.