Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary
ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.
However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.
I think better in concrete terms, so based on your pre-edit question, I'll call Table1
Guardians (because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2
Students (similarly, avoiding the word "children"). The goal is to ensure that any students that do not have any guardians get deleted.
If polygamy and divorce are unheard on, one option would be to have two NULLable fields in
Parent2, to allow for step-parents, gay marriage, etc.). Set these to reference
ON DELETE SET NULL, and add an
ON UPDATE trigger to
Students to delete a record if both fields become NULL. If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database. This assumes that a
CASCADE UPDATE event triggers triggers, I don't know that for a fact.
A more robust solution would be to create a third table relating
Students. It would include fields
EffectiveDate (these three making up a candidate key),
StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds. This would allow for death or divorce: just set the old record's
StopDate. Insert a new record if the remaining guardian ever remarries. You could create an
ON UPDATE trigger and an
ON DELETE trigger on this third table, checking to see if there are now any students which lack active guardians.
However, before putting too much engineering into this, consider: do you really need to cascade? Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer. For that matter, does it even make sense to delete students? If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.