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.
The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:
CASCADE
Automatically drop objects that depend on the table (such as views).
(Emphasis mine.)
When you are dropping a table that is referenced by another table, the object that immediately depends on the table being dropped is not the other table itself but the foreign key constraint defined on it.
So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:
DROP TABLE ... CASCADE
drops the views that directly depend on the table you are dropping.
DROP DOMAIN ... CASCADE
drops the columns that directly depend on the domain you are dropping.
TRUNCATE ... CASCADE
is also consistent with the explanation above because it removes rows, and the objects dependent on rows can only be other rows, including other tables' rows – that is why the referencing tables are truncated as well1.
1My only issue with the other tables' truncation is that their foreign keys may be defined on nullable columns and yet the tables are still truncated completely even if some of their rows do not reference the table(s) specified in the TRUNCATE statement. Still, even if it is an inconsistency, it is how the behaviour is documented:
CASCADE
Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE
.
Best Answer
I don't think that's how
CASCADE
works with MySQL derivatives!Tables:
Some values:
Then:
Now:
So, (for the MySQL family) the DRI (Declarative Referential Integrity) works on the level of records and not on a table level. The referential constraint exists on the table bar and not on foo - therefore deleting foo has no effect on that constraint - and the system won't let you delete the parent table, but will let you delete the parent record.
Take a look at the fiddle here and it's fairly clear (and actually logical enough in one sense) if you reflect on it - at least to me it is!
However, a big caveat to the logic argument - PostgreSQL also doesn't allow
DROP TABLE foo;
but with a far more informative error message:
however
does succeed.
So, MySQL fails on both statements, whereas PostgreSQL's solution is (IMHO and as usual) superior - it "forces" the
CASCADE
if it's explicitly invoked. MySQL won't let it delete under either circumstance.