In this chapter
We'll see what happens when a customer gets deleted but their unpaid tab doesn't — and meet the foreign key link that stops that from happening silently.
The Problem in Real Life
Mike spends a slow afternoon tidying up the Customers table. Maria hasn't shopped at GreenMart in over a year — so Mike deletes her row. Feels like cleanup, not damage.
A week later, Sarah pulls up the sales report and finds a purchase — a loaf of bread, still unpaid — attached to Customer ID 103. There's no Customer ID 103 anymore. There's no customer to even ask for the money.
| Customer ID | Customer | Phone |
|---|---|---|
| 101 | Alex | 555-0198 |
Maria — Customer ID 103 — used to be here. Mike deleted her row last week, without checking anything else first.
| Customer ID | Product | Price | Date |
|---|---|---|---|
| 101 | Apples | $4 | Aug 2 |
| 103 | Bread | $3 | Aug 23 |
This row still says Customer ID 103 — but 103 doesn't exist in Customers anymore. It's pointing at nothing.
Who even is customer 103 anymore?
Sarah
Orphaned Row vs. Foreign Key
A link points at nothing
The Sales row still says Customer ID 103 — but 103 doesn't exist anymore.
Reports stop making sense
"Who bought this?" no longer has a real answer.
Nothing warns you it happened
The delete succeeds instantly and silently — nobody notices until someone hits the orphaned row.
One orphan invites more
Once one dangling reference exists, nothing stops a second, or a whole pattern of them.
What Are Foreign Keys and Referential Integrity, Really?
To the database, Sales.Customer ID was never really "a link to a customer" — it was just a number, the same as Price or Quantity. Nothing about deleting a row in Customers automatically checks whether some other table still mentions it. Mike's delete succeeded instantly, with no warning, because nothing was watching for this at all. A row like Maria's old sale — pointing at an ID that no longer exists — is called an orphaned row.
The fix has a name: a foreign key. A foreign key is a column in one table that's meant to match a primary key in another table — here, Sales.Customer ID pointing at Customers.Customer ID. On its own, that's just a description. What makes it useful is the promise attached to it: referential integrity — the guarantee that a foreign key's value always matches a real, existing row in the other table, enforced by the database itself, every single time, not by whoever happens to remember to check.
Once Sales.Customer ID is declared as a real foreign key, the database starts paying attention to exactly the situation Mike just walked into — someone deleting a row that something else still depends on. And it gives GreenMart an actual choice about what should happen next:
- RESTRICT — block the delete outright while anything still references that row. This is what GreenMart should choose here: losing track of who owes money is worse than a delete failing.
- CASCADE — delete the dependent rows too. Convenient, but dangerous for something like sales history — it would have erased Maria's entire purchase record along with her.
- SET NULL — keep the dependent row, but blank out the link. The sale would survive, just with no customer attached — better than an orphan, but still a fact quietly lost.
| Attempted Action | Result |
|---|---|
| DELETE Customer 103 (Maria) | Blocked — still referenced by 1 sale |
| DELETE Customer 101 (Alex) | Allowed — no sales reference this customer |
The exact mistake that opened this chapter can't happen silently anymore. Deleting a customer with no open sales still works fine — it's only blocked when something would be left pointing at nothing.
For GreenMart, RESTRICT is the obvious choice: a delete that fails with a clear reason is a minor annoyance. A sale silently pointing at nobody is a real accounting problem, discovered weeks later by accident. With referential integrity turned on, trying to delete Maria while Bread's sale still references her wouldn't quietly succeed anymore — the database would refuse, and say exactly why.
Notice what actually changed here: nothing about the Customers or Sales tables themselves. The columns are the same. What's different is a rule the database now enforces on GreenMart's behalf, every time, without anyone having to remember to check by hand.
Key Takeaway
A foreign key isn't just a link between two tables — it's a promise the database enforces: nothing is ever allowed to point at a row that doesn't exist.
Why This Matters
This is the exact mechanism every join in Act 3 depends on — a join only works because a foreign key reliably points at a real row somewhere else. It's also the reason a schema evolves carefully instead of freely: once other tables reference a table's primary key, deleting or restructuring that table has consequences the database will now actively enforce, not just quietly allow.
GreenMart's tables can no longer silently disagree about whether a customer exists. But nothing yet stops someone from leaving a phone number blank, typing a negative price by mistake, or adding a sale with no date at all — that's exactly where the next chapter starts.