Why You Should Love Foreign Keys

October 24, 2007 - 5 minute read -
intent naming

Be Smart

Databases are smart. Relational theory is based in math. The people who invented the relational theory and the people who design and build databases are smart. There is all kinds of really hard math that goes into proving algorithmic efficiency in database development. The people that do that are smart. They are a lot smarter than you (unless you are one of those people - then they are as smart as you). You should use the work of smart people because it will make your work better and your life easier.

Seriously though. As software developers we think about Objects and procedures and abstractions and all the kind of things that we love to do when we write software. Encapsulation, generalization and abstraction aren't just fun to think about - they make building, maintaining and understanding systems much easier. What we often don't think about is the database.

I've seen a number of systems that treat databases as not much more than a simple flat file store. Oracle, IBM, the open source world, and even Microsoft have hired a bunch of smart people and spent HUGE amounts of time and money to make their databases robust, correct, atomic and all kinds of other good adjectives. They contain stored procedures, user defined functions, views, indexes and all kinds of tools that you can use to maintain data. Please learn and use the database. Don't waste all that work and talent.

An Appeal by Example

(This is foreshadowing)

Let's say you're writing a function:

public boolean isCreditCardNumberValid(String ccNumber) {
    if (null == ccNumber || "".equals(ccNumber))
        return false;
    return performLuhnCheck(ccNumber);
}

public boolean performLuhnCheck(String number) {
    int sum = 0;
    boolean alternate = false;
    for (int i = number.length() - 1; i >= 0; i--) {
        int n = Integer.parseInt(number.substring(i, i + 1));
        if (alternate) {
            n *= 2;
            if (n > 9) {
                n = (n % 10) + 1;
            }
        }
        sum += n;
        alternate = !alternate;
    }
    return (sum % 10 == 0);
}

This method is an example of encapsulating a complicated algorithm in a descriptive method name. Understanding what the isCreditCardNumberValid method does does not require that you understand the implementation of the Luhn algorithm. With a fairly simple method like isCreditCardNumberValid this is a moderately valuable thing. With a more complicated method, with many steps, this kind of clarity becomes priceless.

Image instead you saw:

public boolean validate(String str) {
    int sum = 0;
    boolean alternate = false;
    for (int i = str.length() - 1; i >= 0; i--) {
        int n = Integer.parseInt(str.substring(i, i + 1));
        if (alternate) {
            n *= 2;
            if (n > 9) {
                n = (n % 10) + 1;
            }
        }
        sum += n;
        alternate = !alternate;
    }
    return (sum % 10 == 0);
}

Without knowing that the method is a Credit Card validation routine and without a nice name referencing Luhn, I dare you to figure out that this is a Luhn check. I hope it's obvious to see what a small change can make in terms of clarity.

Why You Should Love Foreign Keys

Foreign Keys provide data integrity to your database. It is one of those smart things that allows a database to protect itself from application bugs and user errors. That on it's own should be enough to make you like foreign keys. But I said Love, as in, want to marry them.

The other thing that Foreign Keys do is document the relationships of the tables in the database. They are the equivalent of a bit of encapsulation and good naming - they make the entire database easier to understand. They often even let tools generate ERD diagrams for people that come after you so that they can understand your intent. Anything that lets someone later generate a picture is worth doing because it replaces reams of handwritten documentation.

Just like with code, naming conventions can go a long way to providing clarity in a data model. I would encourage sticking to some reasonable naming conventions in all of the development that you do. But naming conventions can't generate diagrams and naming conventions need someone to enforce them.

Foreign keys in this case are somewhat like Unit Tests. They're functional, but they are also there to document the intent of the designers and developers of the system. You can not ignore them or forget about them if the rules change. As with unit tests, you have to keep them up to date with the system as a whole or they will break. As with Unit Tests you might not see a huge value in them today when you are certain (right?) that your code works. But they play a huge rule in keeping a system running and allowing people to make changes with the confidence that if they did something wrong, they will know it quickly.

Do you love Foreign Keys? I Do!