Stage 2 · Lesson 4 of 11
Keys, constraints and relationships
are rules the database itself enforces. Then bad data cannot get in, whichever part of your code writes it. A primary key identifies each row.
Use a generated bigint or a , not something that can change, like an email. A foreign key guarantees that an order points at a real customer. ON DELETE decides what happens to the order when its customer is deleted: RESTRICT, CASCADE or SET NULL.
NOT NULL, UNIQUE and CHECK (quantity > 0) cover most other rules. Pick column types carefully too: numeric for money and timestamptz for times.
Common traprelying on checks in your code alone. Two requests checking "is this email taken?" at the same moment can both pass, and only a unique constraint stops the duplicate.
A question to check yourself, an AI practice session and 1 cheat sheet entry come with the full roadmap. Unlock
Resources for this stage
Start with· follow one of these
- CoursePaidGrokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
The modelling and normalisation decisions that stay with a codebase for years
- CourseFreeCS50's Introduction to Databases with SQL
Harvard University · Beginner
Free course from querying through schema design, indexes, transactions and scaling
Practise
Also worth your time
- Database Indexing Explained: B-Tree, Hash, Bitmap, R-Tree, and When to Use Each (opens in a new tab)Read
B-tree and composite indexes, plus the leftmost-prefix rule that trips people up
What sits under your queries: storage engines, transactions and isolation levels