Memory Refresh
SQLAntipatterns



No foreign keys





Antipattern: Skipping foreign key

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'; -- SUCCES 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. Skipping foreign key constraints means that it's your responsibility to write code to 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) REFERENCES Accounts(account_id), FOREIGN KEY (status) REFERENCES BugStatus(status) ); Instead of searching for and correcting 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. 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. 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.