The Relational Model

By the end of this session you will be able to:

• Say which properties of a table carry meaning and which ones are accidents you must never depend on
• Find the candidate keys in a table you did not design and choose a primary key from them
• Prove on a running PostgreSQL 18 server that row order changes by itself, and explain what changed it

Get a database you can break

Everything below is meant to be run, not read. Start a throwaway PostgreSQL 18 server with Docker:

docker run --name webcraft-db -e POSTGRES_PASSWORD=lesson -d postgres:18

Give it a few seconds, then open a shell on it:

docker exec -it webcraft-db psql -U postgres

You are now at a psql prompt talking to PostgreSQL 18.4, the current stable release. When you are done, \q leaves psql and docker rm -f webcraft-db destroys the server and everything in it. Starting over takes twenty seconds, so break things freely.

A table is a set of rows

The relational model, in one sentence: a table is a set of rows, and each row is a set of named values.

Both halves of that sentence cost people money when they forget them.

"Set of rows" means the rows have no order. Not "the order is usually insertion order", not "the order is stable unless something happens". There is no order. A table with three rows in it does not have a first row. If you want a first row, you have to state what "first" means, and until you do, the question has no answer.

"Named values" means columns are addressed by name, not by position. full_name is a column because of what it is called, not because it is the second one. Two tables with the same columns in different orders hold the same information.

So what does carry meaning in a table? Exactly three things: the name of the table, the names and types of its columns, and the values in its rows. That is the whole list. Everything else you can see on your screen is an artifact of how the server happened to hand the rows to you this time: the order they printed in, the width of the columns, which row appeared at the top.

This matters because the alternative is a bug that passes every test you write. A query that returns rows in the order you expected, on your laptop, on a table with four rows, will keep doing that right up until the table has four million rows and the server decides to read it with two parallel workers. Then it returns the same set in a different order, your report shows the wrong "latest" row, and nothing in your code changed.

Keys are how you address a row

If rows have no position, you cannot point at one by saying "row 3". You point at it by value. A candidate key is a set of columns whose values are unique across every row, and which has no unnecessary columns in it. A table can have several. You pick one and call it the primary key.

Build a small schema to work with. Still at the psql prompt:

CREATE TABLE student (
    student_id  integer PRIMARY KEY,
    full_name   text NOT NULL,
    cohort_year integer NOT NULL
);
 
CREATE TABLE enrollment (
    student_id  integer NOT NULL REFERENCES student (student_id),
    course_code text    NOT NULL,
    grade       integer,
    PRIMARY KEY (student_id, course_code)
);

Two things there are worth naming. First, enrollment has a primary key made of two columns. A key is a set of columns, and there is nothing unusual about a key that needs more than one of them: a student can take many courses and a course has many students, so neither column alone is unique, but the pair is. Second, REFERENCES student (student_id) is a foreign key. It says every student_id in enrollment must be a real student. That is the only mechanism the relational model gives you for connecting two tables, and it is checked by the server on every insert, not by your application when it remembers to.

Load some rows:

INSERT INTO student (student_id, full_name, cohort_year) VALUES
    (1, 'Lina Haddad', 2024),
    (2, 'Omar Nasser', 2024),
    (3, 'Rana Khalil', 2025);
 
INSERT INTO enrollment (student_id, course_code, grade) VALUES
    (1, 'CS101', 88),
    (1, 'CS102', 91),
    (2, 'CS101', 74),
    (3, 'CS101', NULL);

Now try to break the keys and read what the server says:

INSERT INTO enrollment (student_id, course_code, grade) VALUES (99, 'CS101', 60);

ERROR:  insert or update on table "enrollment" violates foreign key constraint "enrollment_student_id_fkey"
DETAIL:  Key (student_id)=(99) is not present in table "student".

The server refused. There is no student 99, so an enrollment for student 99 is not a fact about the world, and the table will not hold it. Do the same with a duplicate pair:

INSERT INTO enrollment (student_id, course_code, grade) VALUES (1, 'CS101', 55);

ERROR:  duplicate key value violates unique constraint "enrollment_pkey"
DETAIL:  Key (student_id, course_code)=(1, CS101) already exists.

Read those two messages closely. Each one names the constraint that fired and prints the exact values that failed. Most database errors you meet this year are this readable, and the single fastest way to get slower at this subject is to skim past them.

To see the keys on a table you did not write, use \d:

\d enrollment

The output lists the columns, then an Indexes: block naming the primary key, then a Foreign-key constraints: block. That is where you look first when handed a strange database.

Try it

Prove to yourself that row order is not yours to keep. Run this, exactly as written:

SELECT student_id, full_name FROM student;
UPDATE student SET cohort_year = 2025 WHERE student_id = 1;
SELECT student_id, full_name FROM student;

The first SELECT prints students 1, 2, 3. The second prints 2, 3, 1.

Success condition: student 1 is now the last row, and you did not sort anything. Nothing about student 1 changed except an unrelated column, and no row was deleted or inserted.

Here is what happened. Postgres never edits a row in place. An UPDATE writes a new version of the row and marks the old one dead. The new version went on the end, so a straight scan of the table now reaches it last. You can watch this directly with the hidden ctid column, which holds the physical location of each row version:

SELECT ctid, student_id, full_name FROM student;

Student 1 sits at (0,4) now instead of (0,1). Do not build anything on ctid, it changes exactly like this. It is only useful here as proof that "which row comes first" is a fact about storage, not a fact about your data.

Common mistakes

• Trusting the order you got back. It worked on your machine because the table was small and the plan was simple. Growth, an UPDATE, a VACUUM, or a parallel scan all change it, with no error and no warning. If order matters to an answer, you must ask for it; that is what ORDER BY is for, and you will use it constantly starting next session.
• Treating SELECT * column order as a contract. Code that reads result columns by position breaks silently the day someone adds a column to the middle of the table. Name the columns you want.
• Picking a primary key out of real-world data. Email, phone number and full name all look unique until a student changes theirs or two people share one. A key must identify a row for the entire life of that row.
• Thinking a primary key sorts the table. It does not. It enforces uniqueness and gives you a way to name one row. Your student table has a primary key and still handed back 2, 3, 1.

Where this goes next

Next session, Your First Queries, is where you start actually asking questions: SELECT, WHERE, ORDER BY and LIMIT, plus how to read a Postgres error message instead of guessing at what upset it. Keep the container running, you will use this same schema.