FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Keys, Relationships and Joins
Keys, Relationships and Joins Identity, referential integrity and relational reads
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Keys, Relationships and Joins
Primary keys answer: which row? A primary key identifies each row uniquely and cannot be NULL. PostgreSQL automatically creates a unique B-tree index for the primary-key columns. The key is both a data-integrity rule and an efficient lookup path. Primary key
CREATE TABLE projects ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL );
Foreign keys answer: does this relationship exist? A foreign key requires values in the referencing column to match a key in another table. This prevents a task from claiming to belong to a project that does not exist. Foreign key
CREATE TABLE tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, project_id bigint NOT NULL REFERENCES projects(id), title text NOT NULL );
Relationships are data rules, not drawing decorations.
projects tasks comments
id PK id PK id PK name project_id FK task_id FK
one project many tasks many comments
tables + constraints + relationships + transactions
Figure - A relational schema stores facts in tables and uses keys/constraints to preserve valid relationships.
Foreign-key actions are domain decisions Action Meaning Typical question
NO ACTION / RESTRICT Prevent parent change/delete when Should a project be deletable while tasks still exist? dependent rows would be invalid.
CASCADE Propagate parent delete/update to Are child rows meaningless without the parent? related rows.
SET NULL Remove the relationship while Can a task exist without a project? preserving the child row.
Cascade behavior can be correct, but it can also turn one delete into a large irreversible data change. Choose referential actions from business meaning, not convenience.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Keys, Relationships and Joins
One-to-many and many-to-many A project has many tasks when tasks.project_id references projects.id. A many-to-many relationship usually becomes an explicit join table containing two foreign keys, often with a composite primary key or unique constraint. Many-to-many join table
CREATE TABLE task_labels ( task_id bigint NOT NULL REFERENCES tasks(id), label_id bigint NOT NULL REFERENCES labels(id), PRIMARY KEY (task_id, label_id) );
JOIN reads related facts Inner join
SELECT t.id, t.title, p.name AS project_name FROM tasks AS t JOIN projects AS p ON p.id = t.project_id ORDER BY t.id;
The join condition states how rows relate. An INNER JOIN returns rows that have matches on both sides. A LEFT JOIN keeps rows from the left side even when no right-side match exists. Choosing a join is therefore part of the question being asked, not merely SQL syntax.
Foreign keys and indexes are different responsibilities The referenced primary/unique key is indexed, but PostgreSQL does not automatically create an index on the referencing foreign-key column. If the application frequently joins, filters, updates or deletes through that foreign key, an index on the referencing column is often useful. We justify it from query patterns rather than from habit.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “Keys Relationships and Joins” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “Keys Relationships and Joins” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Write joins by hand and predict row counts before executing them.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.N+1 query patterns hidden by application loops
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.