TL;DR: Prove that in practice, the execution of the
alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of the statement itself; consider data changes after the fact irrelevant. (By analogy: opening the stable door is the harmful action, not the horse bolting.)
My boss and I are having a difference of opinion about whether or not to use foreign keys in a MySQL/InnoDB database. I'm for using them for enforcing RI and taking advantage of
ON DELETE CASCADE and
ON UPDATE RESTRICT/
SET NULL, while he's against (confident that he can enforce RI at the application level).
His argument is chiefly:
- Drupal doesn't use them and gets along fine without them, so why should we?
- They're too inflexible when you need to change data and/or structure.
- He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them.
My arguments are chiefly:
- Not all of the databases/ DB engines supported by Drupal 5-7 (MyISAM, SQLite 3) enforce FKs by default, so Drupal leaves them as documented only, whereas this might be different in D8.
- Yes, they can be a pain to deal with, but perhaps the fault lies with poor planning/designing on the part of the developer, not the FKs.
- Surely not enforcing RI with FKs at the DBMS level is more likely to cause data corruption than otherwise. (Case in point is D6's user reference module not restricting changing user status when existing content references a user that must have a certain status).
- It's a waste of time and resources to make code do/attempt what the DBMS already does. How can he guarantee that his code will work as well as (or better than) the DBMS?
Essentially, he'll come over to my way of thinking if I can prove that the act of removing a foreign key itself (regardless of subsequent data inserts/updates) won't corrupt existing data. I can't see a way clear to prove their usefulness/advantage because I'm not sure how I would satisfactorily observe/document the effect a
drop foreign key would have immediately after execution other than comparing data from before execution to data after execution.
So, how do I persuade my boss that we should use foreign keys by proving that the act of later removal/alteration of them won't corrupt existing data, whereas using them will not cause any issues? How would I best set up practical usage tests?
Note: I don't so much want to prove that I am right (from an egotistical point of view) to use FKs, but that it benefits the data and the application code more to use them at the DBMS level than at the application code level.