Tables, rows and columns
A relational database keeps data in tables. This one, books, holds one kind of thing. Each row is one book. Each column is one fact that every book has: its id, its name and who wrote it.
CREATE TABLE names each column and gives it a type: id is a whole number (integer), name is text. NOT NULL means every row must have a name: it can never be missing (NULL). PostgreSQL refuses a value of the wrong type.
A table is a set of rows, not a numbered list. Rows have no fixed order, and the DBMS may hand them back in any order unless a query says ORDER BY. So a row is never found by its position.
Primary keys
id is the primary key: a value that picks out exactly one row. PRIMARY KEY makes two promises: no two rows share an id, and no row is missing one. So book 3 always means Holes.
The front desk tries to add Dune Messiah with id 3. PostgreSQL refuses: a row with key 3 already exists. Nothing is stored, so id 3 still means only Holes.
Foreign keys
Who wrote each book? Typing the author’s name into every book row repeats it, and one typo would split an author in two. So authors get their own table, with their own primary key.
author_id is a foreign key. REFERENCES authors(id) says every value must be the id of a row in authors. Each number is a pointer: 1 to Frank Herbert, 2 to Neil Gaiman and 3 to Louis Sachar.
Coraline has author_id 2. Follow it to the authors row with id 2: Neil Gaiman. Neverwhere points to the same row. One author, many books: the name is stored once and shared.
Next the desk tries Dune Messiah with the free id 5, but author_id 9. There is no author 9, so PostgreSQL refuses again. A foreign key never points to a missing row.
Third try: id 5, which is free, and author_id 1, Frank Herbert. Both keys check out, so the DBMS stores the row. Every book still has exactly one id and a real author.
What happens
;; step 1/10 · table table books id name author_id 1 Dune 1 2 Coraline 2 3 Holes 3 4 Neverwhere 2 4 rows one per book 3 columns one per fact textbook words for the same things: table = relation row = tuple column = attribute
SQL does not promise any order for the rows of a table or of an answer unless the query says ORDER BY. The figure draws them in id order. SQLite checks foreign keys only after PRAGMA foreign_keys = ON on each connection; without it, the book with author_id 9 would be stored. Its error messages are shorter than PostgreSQL’s. SQLite is also looser about types: an ordinary table stores a value of the wrong type, such as text in an integer column, unless the table is declared STRICT.