Let Database Constraints Catch the Bad Data Your Code Missed

A practical approach

Step 1

For each table, list the rules that must always be true: required fields, uniqueness, valid ranges and relationships.

Step 2

Express each rule as a constraint: NOT NULL, UNIQUE, FOREIGN KEY or CHECK.

Step 3

Before adding a constraint to an existing table, query for rows that already violate it and fix them.

Step 4

Catch constraint violations in the application and translate them into clear user messages, such as 'this email is already registered'.

Step 5

Prefer a unique constraint over a 'check, then insert' pattern in code, which allows duplicates when two requests arrive together.

Worked example

A registration endpoint checks whether an email is already registered for a session before inserting. Under load, two requests for the same learner arrive within milliseconds, both checks pass and the learner is registered twice and charged twice. Adding a unique constraint on (session_id, email) makes the second insert fail, and the application returns the existing registration instead of creating a duplicate.

A mistake to avoid

Do not add constraints to large production tables without checking how your database applies them. Some operations scan or lock the table; many databases offer ways to add a constraint without blocking writes, such as validating it in a separate step.

Try it yourself

Pick one important table and write down five rules that should always hold. Query for existing violations of each, then add the constraints in a migration and test that invalid inserts fail.

Your implementation checklist

• Required fields are NOT NULL
• Natural uniqueness rules have UNIQUE constraints
• Relationships use foreign keys
• Value rules use CHECK constraints
• Constraint errors become clear user messages