06 — Constraints and Keys: The Rules the Data Lives By

"Database bureaucracy" was how I filed constraints and skipped past them, until bad data found its way in. Writing them down reframed the whole idea: constraints are rules enforced at the storage layer, so invalid data is rejected at the door instead of corrupting reports and breaking joins downstream. [1]

The framing that clicked is the shift in who validates. Without constraints, every application that writes to the database has to remember and re-check the rules — and the first one to forget leaves a duplicate email or an orphaned order in the table. With constraints, the rule lives in the schema once, and the database refuses anything that violates it. The check moves from "hope the app remembers" to "the engine physically cannot store it."

PRIMARY KEY — unique identity

A primary key is the unique identifier for each row: no two rows share its value, and it can never be NULL [2]. By convention it's an id column.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  ...
);

The job is entity integrity — every row can be precisely located and referenced. A primary key is what a foreign key in another table points at.

FOREIGN KEY — enforce the relationship

A foreign key is a column (or set of columns) that references the primary key of another table [3]. Its job is referential integrity: the engine refuses any row whose foreign-key value doesn't exist in the referenced table.

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       DECIMAL(10,2)
);

Now I can't insert an order for customer_id = 999 if customer 999 doesn't exist — no orphaned orders. And on delete, I can choose what happens to the children: ON DELETE CASCADE removes the orders when the customer goes; ON DELETE RESTRICT blocks the customer deletion until the orders are gone. The rule lives where the relationship lives.

UNIQUE — no duplicates

UNIQUE guarantees that every value in a column (or combination of columns) is distinct [4]. Unlike a primary key, a table can have several UNIQUE columns and they may contain NULL (multiple nulls are usually allowed, since "unknown != unknown").

email VARCHAR(255) UNIQUE

This is the constraint for fields like email or username, where duplicates are a business error regardless of identity.

NOT NULL — value required

NOT NULL simply rejects NULL for that column — every row must carry a real value [5].

name VARCHAR(100) NOT NULL

The subtlety is how NULL behaves: it's not the empty string or zero, it's "unknown." NULL doesn't equal anything, including itself, which is why WHERE col = NULL returns nothing and I must use WHERE col IS NULL. Marking a column NOT NULL removes that ambiguity for any column where a value is genuinely mandatory.

CHECK — arbitrary rule

CHECK enforces any boolean condition I write [6]:

price DECIMAL(10,2) CHECK (price > 0),
role  VARCHAR(20)   CHECK (role IN ('admin','member','guest'))

This is the constraint for domain rules that keys and uniqueness can't express: positive prices, valid enum values, date ranges. The check fires on every INSERT and UPDATE.

Integrity constraints as a category

The roadmap groups these under data integrity constraints — rules that maintain accuracy and consistency at the database level [7][8]. The benefit is consolidation: rather than re-implementing "email must be unique" in every service that writes a user, I declare it once and every writer — present and future — inherits the protection. The cost is that constraints make some bulk operations slower (every row is checked) and migrations more delicate (changing a rule on existing data can fail until the data is cleaned).

How I use this

The habit I keep is to push every rule that is true about the data into a constraint, and leave only rules that are true about a particular workflow in application code. "Every order belongs to a real customer" is a fact about the data — it's a foreign key. "The first order gets a 10% discount" is a workflow rule — that stays in the app. Splitting that way means the database is self-defending: even a hand-written migration script or a buggy service can't violate the invariants, because the engine checks them on every write.

References

[1] Programiz, "SQL Constraints," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/constraints

[2] TutorialsPoint, "SQL Primary Key," tutorialspoint.com, 2024. [Online]. Available: https://www.tutorialspoint.com/sql/sql-primary-key.htm

[3] Cockroach Labs, "What is a foreign key?," cockroachlabs.com, 2024. [Online]. Available: https://www.cockroachlabs.com/blog/what-is-a-foreign-key/

[4] W3Schools, "SQL UNIQUE Constraint," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/sql_unique.asp

[5] Programiz, "SQL IS NULL and IS NOT NULL," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/is-null-not-null

[6] YouTube, "CHECK Constraint," 2023. [Online]. Available: https://www.youtube.com/watch?v=EeG2boJCXbc

[7] DataCamp, "Integrity Constraints in SQL: A Guide With Examples," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/integrity-constraints-sql

[8] DataHeadHunters, "Integrity Constraints," dataheadhunters.com, 2024. [Online]. Available: https://dataheadhunters.com/academy/integrity-constraints-ensuring-accuracy-and-consistency-in-your-data/