I'm only familiar with SQL Server:
Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.
If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE
or, preferably MERGE
) instead of blowing everything away and starting from scratch.
I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.
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
For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.
Here are a few references on triggers:
MSSQLTips
Microsoft's documentation
Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.
Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.
Microsoft's Nested triggers
Info from Paul White on triggers
DML triggers for multiple data rows