MySQL Keep Data until no child tables attached

I'm a bit of a MySQL noob so please bear with me, I'm trying to find a way to keep something in Parent Table and require at least one child table have a foreign key tied with it. Hopefully an example makes sense.

                         Parent Table
                         ------------
                         theId int
                         someValue varchar(20)
                       /       |            \
                      /        |             \
                     /         |              \
Open Table          /    Completed Table       \ Monitoring Table
------------------ /     ------------------      ------------------
childTableId int         childTab开发者_高级运维leId int        childTableId int
someOtherData blob       someOtherData blob      someOtherData blob
theId int (fk)           theId int (fk)          theId int(fk)

I have the 3 child data's all tracking different things and those records are only present for a certain amount of time, however the parent table is always the same throughout all stages. I need a way to make sure a child data can't be deleted if it's the only child table referencing the parent table (there can be 2 at times).

Help?

Depending on the event (open, completed, monitoring), you'll be calling one of these:

DELETE FROM Open
WHERE theId = 'yourId'
AND theId IN
    ( SELECT theId FROM Completed ) UNION ALL
    ( SELECT theId FROM Monitoring );

DELETE FROM Completed
WHERE theId = 'yourId'
AND theId IN
    ( SELECT theId FROM Open ) UNION ALL
    ( SELECT theId FROM Monitoring );

DELETE FROM Monitoring
WHERE theId = 'yourId'
AND theId IN
    ( SELECT theId FROM Open ) UNION ALL
    ( SELECT theId FROM Completed );

This will delete a record only if an identical theId is found in another table. You have to run this within a transaction. Otherwise, several users may be deleting a record with identical theId in different tables simultaneously.