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 example schema is built around.

Open official example (opens in new tab)

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.

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.

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 NULL

assignee_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 the initial migration

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…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. What does a primary key do?

Concept

Uniquely identify each row in a tableCache the results of every query run against the tableAutomatically create a foreign key to every related tableSort the rows permanently in ascending id order on disk

2. What does a foreign key enforce?

Syntax

That the two tables always share identical column namesThat the referenced row can never be updated once createdThat the child table is automatically indexed for full-text searchThat a column value matches a key in another table (or is null if allowed)

3. In this course schema, what is tickets.assignee_id?

Practical

A required foreign key that every ticket must point at a userA unique constraint so each user can hold only one ticketA nullable foreign key to users.idA primary key formed together with the ticket id

4. What does ON DELETE SET NULL mean for assignee_id?

Logic

Deleting a user is blocked until you manually reassign their ticketsDeleting a user clears assignee_id on their tickets instead of deleting those ticketsDeleting a user resets assignee_id to a default user id insteadDeleting a user cascades and removes their tickets as well

5. Can a primary key value repeat across rows?

Concept

Yes, as long as another column differs so the pair is uniqueYes, repeats are allowed but only NULL keys are forbiddenYes, Postgres keeps only the most recent row with that keyNo — uniqueness is required

6. Why use foreign keys instead of unchecked UUIDs?

Practical

The database rejects orphan references automaticallyThe database creates the missing parent row for you automaticallyThe database indexes the UUID so joins never need a WHERE clauseThe database converts the UUID into an auto-incrementing integer

7. What is a composite primary key?

Syntax

A primary key that doubles as a foreign key to another tableTwo separate primary keys declared on the same tableA primary key built by concatenating two columns into one stringA primary key made of more than one column together

8. Does adding an index alone create a foreign key?

Logic

No — but a UNIQUE index is automatically promoted to a foreign keyNo — an index only enforces NOT NULL, not referencesNo — you must add an index before a foreign key will enforce anythingNo — FK is a constraint; indexes often support lookups but are not the same

9. What should happen before inserting a ticket with assignee_id?

Concept

The users table must first have a matching index on emailThe referenced user row should already exist (unless assignee is null)You must disable the foreign key, insert, then re-enable itThe ticket id must equal the referenced user id

10. Which statement about UNIQUE vs PK is true?

Practical

A table has one primary key; it may have additional UNIQUE constraintsA table may declare several primary keys but only one UNIQUE columnA UNIQUE constraint permits one NULL, so it enforces nothingA primary key is just a UNIQUE constraint that also allows NULLs

11. When a referenced user is deleted, what happens to their tickets given this FK?

Conceptadvanced

ALTER TABLE tickets
  ADD CONSTRAINT fk_assignee
  FOREIGN KEY (assignee_id) REFERENCES users(id)
  ON DELETE SET NULL;

The tickets remain but their assignee_id becomes NULLThe delete is blocked because the tickets still reference the userThose tickets are deleted along with the user rowassignee_id is reset to the column default rather than NULL

12. The second insert fails. Why?

Syntaxintermediate

-- id is the PRIMARY KEY
INSERT INTO users (id, email) VALUES (1, 'a@x.co');
INSERT INTO users (id, email) VALUES (1, 'b@x.co');

The primary key must be unique, so a duplicate id violates the constraintThe email column is UNIQUE, and both rows share an email domainPostgres requires primary key values to increase monotonicallyThe second INSERT lacks a RETURNING clause required after the first

13. What happens with this insert if assignee_id has a FK to users(id)?

Practicaladvanced

-- no user with id 999 exists
INSERT INTO tickets (id, assignee_id)
VALUES (gen_random_uuid(), 999);

It inserts but marks the row invalid until user 999 is createdIt inserts and silently sets assignee_id to the nearest existing idIt succeeds because FK checks only run at transaction commitThe insert is rejected because no matching users row exists

14. What does this composite primary key enforce?

Logicintermediate

CREATE TABLE ticket_tags (
  ticket_id uuid,
  tag_id uuid,
  PRIMARY KEY (ticket_id, tag_id)
);

Each (ticket_id, tag_id) pair is unique; the same pair cannot repeatticket_id and tag_id must each be globally unique on their ownOnly the first column, ticket_id, is enforced as the real primary keyA pair may repeat as long as it is inserted in a new transaction

15. What does the UNIQUE on email allow that a second PRIMARY KEY would not?

Conceptintermediate

CREATE TABLE users (
  id uuid PRIMARY KEY,
  email text UNIQUE
);

A table may have two primary keys as long as one is marked UNIQUEUNIQUE on email makes email the table primary key automaticallyUNIQUE forbids NULL emails just as the primary key forbids NULL idsA table has one primary key but can have additional UNIQUE constraints such as email

Checking your session…