Tables, rows and keys
A relational database keeps each kind of thing in its own table of rows and typed columns. Rows have no order, so a primary key names each row, and a foreign key lets a row in one table point at a row in another. Watch PostgreSQL refuse a duplicate key and a key that points nowhere, and store the row that keeps both rules.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01What two promises does PRIMARY KEY make about the id column?
In PostgreSQL a primary key means UNIQUE and NOT NULL together, so each id picks out exactly one row. Pointing at another table is the job of a foreign key.
02You run SELECT * FROM books; twice with no ORDER BY. What is promised about the order of the rows?
A table is a set of rows with no built-in order. PostgreSQL's documentation says that without ORDER BY the rows are returned in an unspecified order.
03books already has a row with id 3 (Holes). What happens to this insert? INSERT INTO books VALUES (3, 'Dune Messiah', 1);
The primary key rule is checked on every change. The insert is refused and nothing is stored, so the id still names only one row.
04authors has ids 1, 2, 3. Which insert does the foreign key on books.author_id refuse?
REFERENCES authors(id) means every author_id must be the id of a row in authors. There is no author 9, so the row would point nowhere and PostgreSQL refuses it.
05Why store authors in their own table instead of typing the author's name into every book row?
Coraline and Neverwhere both hold author_id 2, which points at the one row for Neil Gaiman. Change the name there and every book sees the change.
Glossary
- Relational database
- A database that keeps data in tables (called relations) and links them through key values.
- Table
- A named set of rows that all have the same named, typed columns. Textbooks call it a relation.
- Row
- One entry in a table, such as one book. Textbooks call it a tuple. Rows have no built-in order.
- Column
- One named fact that every row has, with a fixed data type, such as name text. Textbooks call it an attribute.
- Data type
- The kind of value a column accepts, such as integer (whole numbers) or text. PostgreSQL refuses a value of the wrong type; SQLite stores it anyway unless the table is declared STRICT.
- Primary key
- A column (or set of columns) whose value picks out exactly one row: unique and never NULL.
- Foreign key
- A column whose values must match the primary key of a row in another table, so a row can point at another row.
- NULL
- The marker for a missing value. NOT NULL forbids it in a column.
Notes and sources
- 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.
- Strictly, an SQL table with no key can hold two identical rows, so it is a multiset rather than a set. The relational model, and any primary key, rule that out.
- books_pkey and books_author_id_fkey are the names PostgreSQL makes up for these two rules when CREATE TABLE does not name them. You can choose your own with CONSTRAINT name.
- A foreign key column may also be left missing (NULL) unless it is declared NOT NULL; a NULL author_id points to no row and is not checked. Only a value that is present must match.
- 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.
- NOT NULL forbids only the NULL marker. An empty text '' is still a value, so NOT NULL allows it; a CHECK rule such as CHECK (name <> '') is needed to refuse it.
- The rule works both ways: PostgreSQL also refuses to delete an author while books still point to them, unless the foreign key says what should happen instead (for example ON DELETE CASCADE).
- The Python example uses the sqlite3 module that ships with Python. Run it and it prints the two refusals and then the stored row.
- PostgreSQL documentation: Tutorial, Concepts
- PostgreSQL documentation: Constraints (not-null, primary keys, foreign keys)
- PostgreSQL documentation: CREATE TABLE
- PostgreSQL documentation: Sorting Rows (ORDER BY)
- PostgreSQL documentation: Data Types
- SQLite: Foreign Key Support
- SQLite: CREATE TABLE
- Python Library Reference: sqlite3
- E. F. Codd, A Relational Model of Data for Large Shared Data Banks (CACM, 1970)
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 2
Quiz Unlocks after the last step.