Constraints
Declarations the engine enforces: NOT NULL, UNIQUE, CHECK, FK — integrity that survives a bad insert.
Level: beginner
What is it?
Database constraints are rules enforced by the DBMS to keep stored data valid. Common examples include NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints.
Why does it matter?
Applications have multiple writers—APIs, scripts, batch jobs, and administrators. Constraints protect data invariants at the storage boundary instead of trusting every caller to implement validation correctly.
Mental model
Put an invariant as close as possible to the data it protects. An application can still validate early for a better user experience, but the database constraint is the final enforcement layer.
How it works
- NOT NULL forbids a missing value in a column, while CHECK restricts values or row-level relationships according to the database's semantics.
- UNIQUE constrains duplicate key values, with NULL handling varying by database and constraint form.
- PRIMARY KEY combines uniqueness and non-null identity for rows.
- FOREIGN KEY enforces references to an eligible key in another relation.
- Constraint timing and deferrability vary across database systems; check the engine's documentation for complex transactions.
Steps
- Write down the business invariant in precise terms.
- Choose a constraint type that expresses it in the schema.
- Add the constraint through a migration, handling existing invalid rows first.
- Test valid and invalid inserts/updates from different access paths.
Real-world usage
- Protecting unique emails, nonnegative quantities, valid references, and mandatory fields.
- Keeping integrity consistent across API endpoints and scheduled jobs.
Common mistakes
- Treating frontend validation as sufficient for integrity.
- Writing a CHECK constraint without understanding NULL/three-valued logic.
- Assuming a foreign key provides authorization or hides rows from unauthorized users.
Interview questions
Why add database constraints if the API already validates input?
Constraints protect invariants across every writer and prevent races or non-API writes from bypassing application checks.
Is a foreign key an access-control mechanism?
No. It validates referential integrity; permissions and row-level authorization require separate controls.