Theory
Stopping bad data at the door
In a spreadsheet, anyone can type a negative price, leave a name blank, or enter the same customer twice. Nothing stops them, and Meera's totals silently go wrong.
A database refuses. You give it rules, called constraints, and it enforces them on every single insert. Bad data literally cannot get in.
This is the payoff of the whole 'why databases beat spreadsheets' story, and it turns the abstract keys from Unit 3 into real, enforced guarantees. This lesson is the database's immune system.
Theory
A security guard with a checklist
Imagine a security guard at the data's door with a checklist: 'Name filled in? (NOT NULL) Price positive? (CHECK) ID not already used? (UNIQUE) No form? Use the standard one (DEFAULT).' Anyone failing a check is turned away at the door, the bad data never enters. A spreadsheet has no guard; a database has one that never sleeps and never makes an exception.
At a glance
The constraints
| Constraint | Rule it enforces | Example |
|---|---|---|
| NOT NULL | Must have a value | item NOT NULL |
| UNIQUE | No duplicates (null ok) | phone UNIQUE |
| CHECK | Must satisfy a condition | CHECK (price > 0) |
| DEFAULT | Auto-fill when blank | qty DEFAULT 0 |
| PRIMARY KEY | Unique + NOT NULL (one/table) | id PRIMARY KEY |
| FOREIGN KEY | Must match another table's key | customer_id references Customer |
Practical
A table that defends itself
CREATE TABLE sales (
id INT PRIMARY KEY, -- unique + not null
item VARCHAR(30) NOT NULL, -- must be given
category VARCHAR(20) DEFAULT 'Misc',-- auto-fills
price INT CHECK (price > 0), -- no negatives
qty INT DEFAULT 0
);
-- This INSERT is REJECTED: price is negative
-- INSERT INTO sales VALUES (6, 'Salt', 'Grocery', -5, 10);
-- This one is accepted
INSERT INTO sales (id, item, price) VALUES (6, 'Salt', 12);
-- category becomes 'Misc', qty becomes 0 (defaults)This example runs in Gri-Learn on the web, where you can edit it and see the output.
Theory
Foreign keys and ON DELETE CASCADE
The FOREIGN KEY from Unit 3 becomes an enforced rule: an order's customer_id must match a real customer, you cannot create an order for a customer who does not exist (referential integrity).
But what happens when Meera deletes a customer who has orders? ON DELETE CASCADE answers: automatically delete that customer's orders too, so no orphan orders point at a missing customer. Without it, the database would block the delete to protect integrity. Cascade means 'delete the children with the parent'.
Quiz
Meera's table has price INT CHECK (price > 0). She tries to insert an item with price -5. What happens?
- The insert is REJECTED; the CHECK constraint refuses negative prices
- It inserts as -5; CHECK is only a suggestion
- It inserts as 0 automatically
- The whole table is deleted
Show the answer
The insert is REJECTED; the CHECK constraint refuses negative prices
A CHECK constraint is enforced: price > 0 must hold, so inserting -5 is rejected and the bad row never enters. This is the core difference from a spreadsheet, which would happily store -5. Constraints are guarantees, not suggestions; the DBMS refuses any data that violates them. "What does CHECK do?" is a standard exam question.
Think first
Delete a customer with orders
Meera deletes customer 101, who has 5 orders. The orders table has customer_id as a FOREIGN KEY. What happens WITHOUT ON DELETE CASCADE, and what happens WITH it?
Show the answer
Without CASCADE: the delete is blocked, the database refuses, because deleting customer 101 would leave 5 orphan orders pointing at a non-existent customer (violating referential integrity). With ON DELETE CASCADE: deleting customer 101 automatically deletes their 5 orders too, cleanly, no orphans. Cascade lets the deletion 'flow down' to the dependent rows. This is a favourite exam scenario.
Watch out
Where marks leak
Confusing UNIQUE (no duplicates, allows null, several per table) with PRIMARY KEY (unique + NOT NULL, only one per table). Thinking a CHECK is optional, it is enforced, insertions violating it are rejected. Forgetting DEFAULT fills only when no value is supplied. And missing what ON DELETE CASCADE does (auto-delete children) versus the default (block the delete). These constraint distinctions are dense with marks.
Theory
This is why databases are trusted
Banks, railways and Aadhaar run on databases precisely because constraints make invalid data impossible, no negative balances, no duplicate PANs, no orphan records. The keys you designed in Unit 3 become these enforced rules. Two lessons remain: functions that summarise rows (aggregates) and transform values (scalars), then sequences and views to finish the subject. Next: aggregate functions.
Summary
Key takeaways
- Constraints are rules the DBMS enforces on every insert; bad data is rejected at the door.
- NOT NULL demands a value; UNIQUE forbids duplicates (allows null); CHECK validates a condition; DEFAULT auto-fills.
- PRIMARY KEY = unique + NOT NULL, one per table; FOREIGN KEY enforces referential integrity to another table's key.
- ON DELETE CASCADE auto-deletes child rows when their parent is deleted (else the delete is blocked).
- This enforced integrity is exactly what spreadsheets lack.
- Memory hook: a security guard with a checklist turning bad data away at the door.