How do I prove that the act of removing foreign keys doesn't corrupt existing data?

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 insert

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS.

Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your team, does your boss REALLY think that you can write bug-free RI (Referential Integrity) code that has significant functionality in less than 5 years? I certainly don't!

I can't tell you the number of times I've read on other forums (primarily Oracle) where some poor schmuck is crying his eyes out over an app he's inherited where RI was enforced at the app layer. Orphaned records, childless parents, inconsistent data... the list goes on and on... data might as well be Swiss cheese, it's got that many holes! Tell your boss to have a read of this book by database professionals (Oracle experts, some of the best, most readable technical writing I've ever seen). From a review here - an outline of what Jonathan Lewis (a man who wrote a 530 page book on only (get this) the FUNDAMENTALS of the Oracle optimiser.


Chapter 10: Design Disasters, by Jonathan Lewis


More war stories, for fans of Chapter 8! "Now prepare yourself to read
all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.


This chapter describes some of the most common mistakes in development
Oracle database applications. You'll certainly recognise some of them,
because so many people stubbornly cling to certain beliefs. I know I
like to bring up several of his points when I get into common
arguments like these:

1. We want our application to be "Database Independent."

2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

3. We want to use sequences for our primary keys.

Take CAREFUL note of point 2! If your boss persists with this madness, then my advice to you is to run fast and run far! You will spend your days in a miserable hellhole of constant firefighting, and never be able to fulfill your potential as a developer or a DBA and you'll learn very little! Just a few thoughts!

StackExchange Database Administrators Q#74457, answer score: 10