Greg Heo

Foreign keys - an introduction

The least you need to know about foreign keys but were afraid to ask.

Someone recently asked me why I had all these foreign keys defined in the database schema even though “MySQL doesn’t support foreign keys.” After I finished laughing, the statement changed to “foreign keys aren’t needed in real database design anyway.”

I laughed again. Then I cried a little. Then I revoked this person’s license to work in any technical field.

A scenario

Let’s start with the problem that is solved by foreign keys.

You have a database keeping track of employees. There’s a table listing all employees, a table with a list of skills, and another table establishing a many-to-many relationship between employees and skills. So Bob knows C++ and Java while James knows C and C++.

These are all keyed by ID of course, so Bob’s employee ID is 15 and C++ is actually skill ID 75. That means the employee skills table is filled with numbers since it relates an employee ID to a skill ID.

Now there’s a bug in the front-end interface that mangles these IDs. The system tries to insert a new record to relate employee 15 to skill 99; the problem is there is no skill 99. What happens now?

Or, someone goes right into the database and decides that Java is worthless. She deletes Java from the skills table, but what happens to all those records in the skills-to-employees table that are referencing Java?

Foreign keys are constraints

You may have already heard about unique keys, like primary keys. That means values must be unique. The constraint on the field means there can only be one record with a certain value at a time. With a unique key, the database system checks the table when a new record is inserted to enforce the rule.

A foreign key is another kind of constraint. It’s called “foreign” because it depends on some other table. In our skills and employees example, the skills-to-employees table would have a foreign key constraint linking back to the employee table and the skills table:

SQL table diagram

The result

With foreign keys in place, the system maintains data consistency. If 5 employees have Java listed as a skill, the system will not allow Java to be deleted from the skills table. If there is no skill #99 or there is no employee #101, the system will return an error if you try to insert it into the skills-to-employees table. The insert/delete/update statement is stopped right at the database level.

The code

Here’s how it looks when you’re creating a new table. Like other kinds of keys, you can specify the foreign key with the field itself (as with employee_id) or as its own declaration (as with skill_id):

CREATE TABLE employee_skills (
    employee_id INTEGER REFERENCES employees(employee_id),
    skill_id INTEGER,
    FOREIGN KEY (skill_id) REFERENCES skills(skill_id),
);

And here’s how it looks when you’re modifying an existing table:

ALTER TABLE employee_skills 
    ADD FOREIGN KEY (skill_id) REFERENCES skills(skill_id);

Further reading