Antipattern: Leave Out the Constraints
Explore the risks and challenges of leaving out foreign key constraints in SQL databases. Understand how skipping these constraints causes data inconsistencies, forces manual referential integrity checks, and complicates updates and deletions. This lesson helps you recognize why relying on application code alone is risky and how constraints ensure database consistency.
We'll cover the following...
Even though skipping foreign key constraints may seem at first to make our database design simpler, more flexible, and even speedier, we pay for this in other ways: it makes it our responsibility to write code to ensure referential integrity manually.
Assuming flawless code
Many people’s solution for referential integrity is to write application code so that data relationships are always satisfied. Every time they insert a row, they have to make sure that values in foreign key columns reference existing values in the referenced table. Every time they delete a row, they have to make sure that any child tables are also updated appropriately. In other words, the popular answer is simply to “make no mistakes”.
Furthermore, to avoid referential integrity mistakes when they have no foreign key constraints, they’d have to run extra SELECT queries before they apply the changes, to ensure that the change won’t result in broken references. For instance, to insert a new row, they’d need to make sure that the parent row exists:
They would then add a bug that references it:
To delete a row, they’d have to make sure no child rows exist:
Only after running these steps could they then delete the ...