In this chapter
We'll see what happens when nothing stops bad data from being typed in — and meet four rules (NOT NULL, UNIQUE, CHECK, DEFAULT) that GreenMart's tables now enforce automatically.
The Problem in Real Life
It's delivery day. Sarah is entering a stack of new products by hand, typing fast. One row gets a price with no product name attached. Apples gets entered once in the morning rush, then again after lunch — nobody notices. A third row gets a stray minus sign: Milk, priced at negative three dollars instead of three.
That evening, Mike closes the register and the numbers don't add up. Something in today's new entries is wrong — and nothing in the system ever said so.
| Product | Price | In Stock |
|---|---|---|
| $3 | 15 | |
| Apples | $4 | 40 |
| Apples | $4 | 40 |
| Milk | -$3 | 22 |
Four rows, three separate problems: a product with no name, Apples entered twice by accident, and Milk priced below zero. The table accepted every single one without complaint.
The table let me type all of that? It should've stopped me.
Sarah
Anything Goes vs. Rules the Table Enforces
Blank fields slip through
A product with no name saves just fine — until someone actually needs to find it.
Duplicates go unnoticed
The same product gets entered twice, and nothing ever flags it.
Impossible values get accepted
A negative price saves without a single warning from the table.
Mistakes surface too late
Nobody notices until the register doesn't add up that evening.
What Are Constraints, Really?
Every chapter so far in Act 1 has shaped what a table looks like — its entities, its keys, its links to other tables. None of that stops a person from typing something structurally fine but factually wrong into it. A blank product name, a duplicate entry, a negative price — the table happily accepts all three, because up to now, nothing has ever told it these are actually wrong.
A constraint is a rule attached directly to a column, enforced by the database itself on every single insert or update — not something Sarah has to remember to double-check by hand. This is the same idea chapter 5's referential integrity already introduced, aimed at a different kind of mistake: instead of "does this reference point at something real," a constraint asks "is this specific value even allowed here at all."
Four constraints would have caught every single mistake from today's delivery, the instant each one was typed:
- NOT NULL — a column that can never be left blank. Every product needs a name; this refuses to save a row without one.
- UNIQUE — a column where no two rows are allowed to share the same value. This is what stops the same product from silently being entered twice.
- CHECK — a rule about which values are even valid. "Price must be greater than 0" rejects a negative price before it's ever saved.
- DEFAULT — a value filled in automatically when nobody provides one. A brand-new customer's balance defaults to $0 instead of sitting blank and ambiguous.
| Attempted Row | Result |
|---|---|
| Product: (blank name), Price $3 | Rejected — NOT NULL: every product needs a name |
| Product: Apples (duplicate), Price $4 | Rejected — UNIQUE: Apples already exists |
| Product: Milk, Price -$3 | Rejected — CHECK: price must be greater than 0 |
All three mistakes from delivery day get caught the moment they're typed now — not discovered that evening, once the register is already short.
| Customer | Phone | Balance Due |
|---|---|---|
| Devon | 555-0233 | $0 |
Devon is a brand-new customer — nobody typed a balance for them. The DEFAULT constraint filled in $0 automatically instead of leaving the column blank.
Notice what all four rules have in common: none of them depend on Sarah or Mike remembering anything. A blank name, a duplicate product, a negative price, a missing default — each one is handled by the table itself, the instant it happens, not caught later during a review nobody has time to run.
Constraints don't just prevent mistakes — they encode GreenMart's actual rules about what "valid" data even means, written directly into the table instead of into someone's memory.
Key Takeaway
A constraint isn't a limitation — it's a business rule written directly into the table, enforced automatically, every single time.
Why This Matters
It's worth looking back at chapter 4 with this new vocabulary: a primary key was always secretly two constraints wearing one name — NOT NULL (every row must have one) plus UNIQUE (no two rows can share one). Nothing about that changed; this chapter just gives the reader the words to describe precisely what a primary key was already doing. Looking ahead, a CHECK constraint like "price must be greater than 0" only makes real sense once a column actually holds a number and not just arbitrary text — which is exactly the decision the next chapter is about.
GreenMart's tables now refuse bad data automatically. But NOT NULL and CHECK alone don't decide what kind of value a column should even hold in the first place — a phone number, a price, and a sale date all need to be stored correctly to begin with. That's exactly where the next chapter starts.