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 Students
, FatherID
and MotherID
(or Parent1
and Parent2
, to allow for step-parents, gay marriage, etc.). Set these to reference Guardians
with 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 Guardians
to Students
. It would include fields GuardianID
, StudentID
, 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.
On the face of it, that does seem impossible.
The thing is, your error suggests it's not that you're trying to delete at all.
The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))
It's also possible you have some BEFORE DELETE
trigger magic on survey_main that's doing something unexpected.
Right after this error occurs, try this:
SHOW ENGINE INNODB STATUS;
The LATEST FOREIGN KEY ERROR
section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.
Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.
Also, the version of MySQL you're using may also be relevant, so please mention it.
With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:
SELECT *
FROM survey_answers sa
LEFT JOIN survey_main sm ON sm.id = sa.survey_id
WHERE sm.id IS NULL;
If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.
Best Answer
If you like the
Parent
andChild
terms and you feel they are easy to be remembered, you may like the translation ofON DELETE CASCADE
toLeave No Orphans!
Which means that when a
Parent
row is deleted (killed), no orphan row should stay alive in theChild
table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there isON 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 asAllow No Orphans!
(in the first place). NoChild
should ever be allowed (written) in the child table if it hasn't aParent
(a row in the parent table).For consistency, the
ON DELETE RESTRICT
can be translated to the (less aggresive)You Can't Kill Parents!
Only childless rows can be killed (deleted.)