Antipattern
Skipping foreign key Many developers avoid foreign key constraints. The constraints make it inconvenient to update related columns in multiple table.Delete
For example, 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'; # errors
DELETE FROM Bugs WHERE status = 'BOGUS';
DELETE FROM BugStatus WHERE status = 'BOGUS'; # success
Update
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'; # errors
UPDATE Bugs SET status = 'INVALID' WHERE status = 'BOGUS'; # success
Code
Some developers find these scenarios difficult to manage. So they decide not to use foreign keys at all. Skipping foreign key constraints means that it's your responsibility to write some code. That code ensure referential integrity manually.Solution
Add Constrains
CREATE TABLE Bugs (
reported_by BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
FOREIGN KEY (reported_by) <span class='keyword_code'>REFERENCES</span> Accounts(account_id),
FOREIGN KEY (status) REFERENCES BugStatus(status)
);
Don't search for and correct data integrity mistakes.
You can prevent these mistakes from entering your database in the first place.
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
FK 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)
<span class='keyword_code'>ON UPDATE CASCADE</span>
ON DELETE <span class='keyword_code'>RESTRICT,</span>
FOREIGN KEY (status) REFERENCES BugStatus(status)
ON UPDATE CASCADE
ON DELETE SET DEFAULT
);
This solution allows you to update or delete the parent.
It lets the database takes care of any child rows that reference it.
Updates to BugStatus and Accounts propagate automatically to child rows in Bugs.
There’s no longer a catch-22 problem.
RESTRICT for the FK reported_by means that you can’t delete an account.
You can not delete it if some rows in Bugs reference it.
The constraint blocks the delete and raises an error.
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.
Last update: 222 days ago