--- title: "06 — Constraints and Keys: The Rules the Data Lives By" uid: constraints-and-keys tags: ["sql", "roadmap:sql", "primary-key", "not-null", "check", "constraints", "unique", "integrity", "foreign-key"] excerpt: "Constraints are rules enforced at the storage layer — invalid data is rejected at the door instead of corrupting reports and breaking joins downstream." date: 2026-08-13T03:27:37+0000 source: https://www.aveshina.my.id/en/blog/constraints-and-keys --- "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." ```figure ``` ## 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](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](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/](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](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](https://www.programiz.com/sql/is-null-not-null) [6] YouTube, "CHECK Constraint," 2023. [Online]. Available: [https://www.youtube.com/watch?v=EeG2boJCXbc](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](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/](https://dataheadhunters.com/academy/integrity-constraints-ensuring-accuracy-and-consistency-in-your-data/) ```quiz Q: What does a PRIMARY KEY guarantee? - Every row is uniquely identifiable, and the key column is never NULL - The column can contain duplicates as long as they are not NULL correct: 0 explain: A primary key enforces both uniqueness and NOT NULL, giving entity integrity — every row can be precisely located. Q: An orders.customer_id REFERENCES customers(id). What does the database now refuse? - Inserting an order whose customer_id doesn't exist in customers - Inserting an order with any NULL column correct: 0 explain: The foreign key enforces referential integrity: the referenced customer must exist. It says nothing about other columns being NULL. Q: What's the difference between UNIQUE and PRIMARY KEY? - Nothing; they are identical - A table has one PRIMARY KEY but can have many UNIQUE columns; UNIQUE allows NULL (usually), PK does not correct: 1 explain: PRIMARY KEY implies unique + not null and there's only one per table. UNIQUE columns can be multiple and typically permit NULL. Q: Which constraint would enforce that price is always positive? - CHECK (price > 0) - UNIQUE (price) correct: 0 explain: CHECK enforces an arbitrary boolean condition. UNIQUE only prevents duplicate values; it doesn't constrain ranges. Q: Why does WHERE col = NULL return no rows? - Because NULL equals NULL - Because NULL means "unknown" and never compares equal; you must use IS NULL correct: 1 explain: NULL is not a value, it's the absence of one. Equality with NULL is never true, so the IS NULL / IS NOT NULL predicates exist to test for it. ```