Primary key constraint
"id" uuid NOT NULL DEFAULT gen_random_uuid(),
...
CONSTRAINT "PK_tickets" PRIMARY KEY ("id")Postgres also auto-creates a unique index backing this constraint.
Back to week 2
Topic
Primary & foreign keys
Definition
A primary key is a constraint that uniquely identifies every row in a table, and a foreign key is a constraint that requires a column’s value to match a primary key value in another table (or be null).
In simpler words
A primary key is a row’s permanent ID; a foreign key says “this value must point at a real row somewhere else.”
Keys are what turn separate tables into a connected schema. tickets.assignee_id pointing at users.id is the whole relationship this app is built around.
After this you can
- Explain why every table here uses a uuid primary key
- Trace the foreign key from tickets to users and its ON DELETE behavior
- Say why foreign key columns are usually indexed
Primary keys identify a row
Definition
A primary key constraint guarantees that its column (or columns) is unique and never null across every row in the table, giving each row a stable identity.
In simpler words
It is the value everything else — foreign keys, application code, URLs like /tickets/:id — uses to mean “this exact row.”
Both tables here use PRIMARY KEY ("id") on a uuid column generated by gen_random_uuid().
UUIDs are a deliberate choice: they can be generated before insert, do not leak row counts, and merge safely across environments.
Foreign keys enforce referential integrity
Definition
A foreign key constraint requires that every non-null value in a column already exists as a primary key value in the referenced table, and it specifies what happens to the referencing row when the referenced row is deleted.
In simpler words
It is Postgres refusing to let a ticket point at a user that does not exist, and defining what happens if that user is later removed.
tickets.assignee_id references users.id with ON DELETE SET NULL — deleting a user un-assigns their tickets instead of deleting the tickets.
The alternatives are ON DELETE CASCADE (delete dependents too) and ON DELETE RESTRICT (block the delete) — the choice is a business decision, not just syntax.
Foreign key on tickets
"assignee_id" uuid,
...
CONSTRAINT "FK_tickets_assignee"
FOREIGN KEY ("assignee_id") REFERENCES "users"("id") ON DELETE SET NULLassignee_id has no NOT NULL, so unassigned tickets (null) are valid — only non-null values must match a real user.
Index the columns you look up by
Definition
An index is a separate data structure that lets Postgres find matching rows without scanning the whole table, and foreign key columns are common index candidates because they are frequently filtered or joined on.
In simpler words
A foreign key constraint does not automatically create an index on the referencing column — this schema adds one explicitly.
IDX_tickets_assignee_id speeds up “find all tickets for this user” and the join Postgres does to enforce the FK check.
IDX_tickets_status speeds up filtering the ticket list by status — a query pattern the API uses on every list request.
Indexes from InitSchema
CREATE INDEX "IDX_tickets_status" ON "tickets" ("status");
CREATE INDEX "IDX_tickets_assignee_id" ON "tickets" ("assignee_id");Index columns you filter, sort, or join on — not every column.
Keep in mind
- A foreign key is a promise about existing data, not just a naming convention.
- Decide ON DELETE behavior on purpose — SET NULL, CASCADE, and RESTRICT all mean something different for your users.
- If a column shows up in a WHERE or JOIN often, it is an indexing candidate.
Test
Check your understanding
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2