5

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:

  1. Drupal doesn't use them and gets along fine without them, so why should we?
  2. They're too inflexible when you need to change data and/or structure.
  3. 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:

  1. 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.
  2. 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.
  3. 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).
  4. 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.

  • 2
    "He's removed them from existing tables to change things and it's caused data corruption [...] so he'd rather not use them." - I don't get it. The FKs were removed, the inevitable data corruption showed up and that's why he doesn't want to use them? – a_horse_with_no_name Aug 20 '14 at 15:28
  • While the title question is legitimate, I think the idea behind it (proving you are right) and the way it is presented is a bit unoptimal. Nobody on this site will tell you that referential integrity is wrong and that you shouldn't use foreign keys. Also, it is not difficult to handle them, referential integrity can be disabled at any time in order to perform DMLs. I refuse to believe that your boss told you that they are plainly bad unless he has never used a database. – jynus Aug 20 '14 at 15:30
  • Actually, the people that I have seen not using them for a custom developed application were either poor developers or had to implement them at application side because MySQL implementation was limited (too little constraints or extra locking). – jynus Aug 20 '14 at 15:34
  • @a_horse_with_no_name Either he didn't explain his point properly, I misunderstood it or it's a flawed argument, but that's the impression I got as well. jynus I also refuse to believe that he told me they are bad (more that they are inconvenient), considering he's got more experience as a developer than I do. – Agi Hammerthief Aug 20 '14 at 15:41
  • Interesting enough I never see this discussion when people are using Postgres, Oracle, DB2 or SQL Server. It's alyways MySQL where this discussion comes up. – a_horse_with_no_name Aug 20 '14 at 15:43
10

To counter the points directly:

Drupal doesn't use them and gets along fine without them, so why should we?

Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively meaningless. I know people who don't wear a seatbelt in cars, but I do as do most others...

They're too inflexible when you need to change data and/or structure.

If you are changing your structure to the point where you need to change FK relationships then anything in your existing design can look inflexible (particularly if the existing design didn't properly match the system you were modelling to start with, which is probably why you are looking to alter the existing structure).

They are deliberately inflexible when changing data. They are designed to not let you change data in such a way that integrity could be compromised, even temporarily. Ask for examples where a foreign key constraint would stop a data modification and for each one we'll be able to explain why that is a good thing and how to work with the constraint to achieve your goal without breaking it (or conversely, we might instead tell you why that particular FK relationship is wrong - but that doesn't make other FKs wrong it just means there was a problem with that part of your model).

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.

Removing them did not cause corruption. Removing them allowed a bug in some other code to cause corruption over time and it wasn't noticed. If the keys had been in place that process would have raised an error and presumably the problem with it would have therefore been noticed and fixed instead of silently being allowed to break more data until noticed. All removing the constraint does is stop the database enforcing the constraint for future inserts/updates/deletes - it does not affect existing data.

I'm not sure how you'd go about conclusively proving to someone who is sure otherwise that this is the case.

The only proof I can think of is first principals: reasserting what foreign key constraints actually do thereby showing that they would not allow inconsistency to appear, and if there are specific examples of the corruption the person has in mind you could work through trying to create that problem with the keys in place (showing that the key would have caused an error instead of allowing the inconsistency to be created).

More specifically about the immediate effect of removing the constraints: show that dropping them does not alter existing data at all. Create a copy of the database, drop the keys in the copy, and run a full comparison of the data to show that nothing changed as a result of the constraints being lifted.

he's against (confident that he can enforce RI at the application level).

At best that is bad use of development/testing time, at worst it will create a nightmare for later. You are reinventing the wheel, probably inefficiently and potentially with bugs, you have to implement your new wheel everywhere code touches that data in every application now and going forward instead of simply letting the database handle it each time, and if one of those bits of code has a bug the data potentially loses integrity for all applications for all time.

There are of course complex business rules that a DBMS can't enforce for you, so you have to implement them in your BL layer, but for the fundamentals like this let the DB handle it.

An extra one not mentioned but I've heard several times: They are inefficient when we make changes to the parent objects

This is due to most DBMSs not automatically creating an index for each FK, many people assume that they do and are surprised that they don't see the performance metrics that they expect. DBs don't do this as it could be very wasteful when not needed (which is more often then you might expect). If you need an index on the columns of the FK, to make cascading updates/deletes (or just simple join queries in that direction) efficient, create one.

  • "Removing them did not cause corruption. Removing them allowed a bug in some other code to cause corruption over time and it wasn't noticed." Yes, but how do I prove this (instead of taking it on faith because I was told so)? I'm not disputing your answer, but it doesn't provide any proof of it's correctness. – Agi Hammerthief Aug 21 '14 at 10:47
  • The only proof I can think of is first principals: reasserting what foreign key constraints actually do thereby showing that they would not allow inconsistency to appear, and if there are specific examples of the corruption the person has in mind you could work through trying to create that problem with the keys in place (showing that the key would have caused an error instead of allowing the inconsistency to be created). – David Spillett Aug 21 '14 at 10:58
  • 1
    More specifically about removing the constraints: show that dropping them does not alter existing data at all. Create a copy of the database, drop the keys in the copy, and run a full comparison of the data to show that nothing changed as a result of the constraints being lifted. – David Spillett Aug 21 '14 at 10:59
11

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!

  • 2
    I totally agree. Even if you could get RI right in your application, in my experience any database that contains valuable information will be used by more than just one application. – a_horse_with_no_name Aug 20 '14 at 16:17
  • And any RI will have to reimplemented again and again - hello Groundhog Day! – Vérace Aug 20 '14 at 16:39
  • Thank you for your contribution. While there is truth in this answer and it supports the argument for using foreign keys, it doesn't conclusively answer the question. – Agi Hammerthief Aug 21 '14 at 10:35
  • Stop app (or suitable test copy). Dump database. Drop FOREIGN KEYs. Dump database again. Diff the files. There will be NO difference (in the INSERT INTO TABLE blah VALUES(...) sections of those files). – Vérace Aug 21 '14 at 10:49
  • Thanks again. That's what I was thinking as well. I'd like to mark both this and David Spillett's answer as accepted, but unfortunately only one can be chosen. – Agi Hammerthief Aug 21 '14 at 11:21
2

Removing foreign keys does not damage data because you are doing DDL to the indexes.

Once you do that, data integrity (even for existing data) going down the road needs its integrity tested.

EXAMPLE

create table parent
(
    id int not null auto_increment,
    ...
    primary key (id)
);
create table child
(
    id int not null auto_increment,
    fk_id int not null,
    ...
    primary key (id),
    key fk_index (fk_id),
    CONSTRAINT FK FOREIGN KEY (fk_id) REFERENCES parent(id)
);

Let's say you remove the constraint

create table parent
(
    id int not null auto_increment,
    ...
    primary key (id)
);
create table child
(
    id int not null auto_increment,
    fk_id int not null,
    ...
    primary key (id),
    key fk_index (fk_id)
);

After days of operation, you should run the following queries

ORPHANED CHILD RECORDS

select COUNT(A.id) orphans from child A left join parent B
on A.fk_id = B.id where B.id IS NULL;

If you get a nonzero number for orphans, you got orphaned child records. Removing the constraint could be a possible factor.

PARENTS WITHOUT CHILDREN

select COUNT(A.id) childless_parents from parent A left join child B
on A.id = B.fk_id where B.id IS NULL;

If you get a nonzero number for childless_parents, you got parents without children (Sorry, this sounds rather redundant). Removing the constraint could be a possible factor.

EPILOGUE

In a perfect world, referential integrity would be either be an afterthought or taken for granted. Therefore, if you are going to remove foreign key constraints, please wake up, get the sandman out of your eyes, go find all the constraints in your DB, and check them periodically.

In the case of nonzero orphans, you must delete those records or go assign them to parents.

  • Just as a matter of interest Rolando, why not use something like this as your queries to detect orphans and barren parents? SELECT c.id, c.parent_id, c.identifier from child c where c.parent_id NOT IN (SELECT id FROM parent); and SELECT p.id, p.identifier FROM parent p WHERE p.id NOT IN (SELECT parent_id FROM child); What I'm trying to get at is why you choose your form of the query rather than what seems to me to be somewhat simpler? – Vérace Aug 20 '14 at 22:18
1

Actually I guess the answer is in your boss's words:

3.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.

FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, how does RI at the application level prevent corruption? Something not to discuss, but remember he had to remove the FKs to change data, and possibly in a manner that went against the rules implemented by the droped FKs.

  • "FKs removal does not change data ..." This may be so, but is there any way to prove it conclusively? That's the central point of the question. – Agi Hammerthief Aug 21 '14 at 10:40
1

TL;DR -- explain how db's work

Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it doesn't cause corruption and explain why the chance of it causing corruption is more theoretical than actual.

I would start with why the chance of it actually causing corruption is effectively neligible. A foreign key is meta data, data that the program that is the database engine uses to decide how to treat incoming commands. It is not part of your data, but it is handled in essentially the same manner. A database engine that caused corruption on deleting data about foreign keys, would almost certainly have the same problem with deleting your data (ie deleting data from table [a] would corrupt or delete data from table [b], and your data being larger, this would happen more frequently).

Each row of your data does not have somthing that says "this is a fk from a to b", instead there is a seperate table that has a reference to table a and table b, and the related columns. So, the data that represents a foreign key is not mixed up with your data, it is seperate -- this is why you can have a table with millions, or even billions, of rows and still drop a foreign key constraint in micro-seconds. You can actually use that fact to show that corruption did not take place: create a sample database with a huge number of rows, create a fk, backup the data file, delete the Fk, compare the resulting data file to the original. If the data FK relationship was embedded in your data, all of it would have to change, but it doesn't, which is why it can be so quick. It's also why the size of your database is essentially unchanged when you add or delete a fk constraint. Unlike an index, a constraint exists as just a rule, with no actual data of any significant size associated with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.