Expert Refresh

No foreign keys

1) Many developers avoid foreign key constrains




2) Foreign keys




3) You can't use code script







Context

I designed an equipment-tracking application some years ago using MySQL. The default storage engine for MySQL was MyISAM, which doesn’t support foreign key constraints. As the project evolved, we developed a problem: when referential integrity wasn’t satisfied, discrepancies showed up in reports. The project manager asked me to write quality control scripts that we could run periodically to let us know when discrepancies occurred. Some software developers recommend avoiding referential integrity constraints: - Your data updates can conflict with the constraints - You're using a database design that’s so flexible it can't support referential integrity constraints - You believe that the index the database creates for the foreign key will impact performance - You use a database brand that doesn't support foreign keys

Antipattern

Even though it seems at first that skipping foreign key constraints makes your database design simpler, more flexible, or speedier, you pay for this in other ways. It becomes your responsibility to write code to ensure referential integrity manually. Many developers avoid foreign key constraints because the constraints make it inconvenient to update related columns in multiple tables. For instance, if you need to delete a row that other rows depend on, you have to delete the child rows first to avoid violating foreign key constraints. But this problem is solvable. DELETE FROM BugStatus WHERE status = 'BOGUS'; -- ERROR! DELETE FROM Bugs WHERE status = 'BOGUS'; DELETE FROM BugStatus WHERE status = 'BOGUS'; -- retry succeeds The unsolvable problem is when you UPDATE a column that child rows depend on. You can’t update the child rows before you update the parent, and you can’t update the parent before you update the child values that reference it. It’s a catch-22 scenario. UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS'; -- ERROR! UPDATE Bugs SET status = 'INVALID' WHERE status = 'BOGUS'; -- ERROR! Some developers find these scenarios difficult to manage, so they decide not to use foreign keys at all.

Solution: Constrains

Instead of searching for and correcting data integrity mistakes, you can prevent these mistakes from entering your database in the first place. CREATE TABLE Bugs ( reported_by BIGINT UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'NEW', FOREIGN KEY (reported_by) REFERENCES Accounts(account_id), FOREIGN KEY (status) REFERENCES BugStatus(status) ); The software industry average is 15 to 50 bugs per 1000 lines of code. All other things being equal, if you have fewer lines of code, you have fewer bugs.

Cascading

Foreign keys have another feature you can’t mimic using application code, cascading updates. CREATE TABLE Bugs ( reported_by BIGINT UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'NEW', FOREIGN KEY (reported_by) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE ON DELETE SET DEFAULT ); This solution allows you to update or delete the parent row and lets the database takes care of any child rows that reference it. Updates to the parent tables BugStatus and Accounts propagate automatically to child rows in Bugs. There’s no longer a catch-22 problem. For example, RESTRICT for the foreign key on reported_by means that you can’t delete an account if some rows in Bugs reference it. The constraint blocks the delete and raises an error.

Overhead

It’s true that foreign key constraints have a bit of overhead. But compared to the alternative, foreign keys prove to be a lot more efficient.


References