Antipattern: Leave Out the Constraints

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: