Tables and keys
A relational database keeps data in tables. Each table has a schema: a fixed list of named columns, each with a type. The users table has id, name and city, and each row is one user. CREATE TABLE sets that schema.
The id column is the primary key. Every row must have an id, and no two rows may share one, so id 2 always means exactly one user: Sam. The database management system (DBMS) refuses a second row with id 2.
Ask and change
The app sends SELECT name FROM users WHERE city = 'Oslo'. The DBMS takes the users table, tests each row’s city, keeps the rows that match and gives back only their name column. One row matches, so the answer is Lina.
INSERT adds a row. The DBMS first checks it against the schema: id 4 is not taken and the name is not empty. Only then is Max from Lima stored, as a fourth row.
UPDATE changes values in rows that already exist. WHERE id = 2 picks only Sam’s row, so his city changes from Cairo to Rome. Leave out WHERE and every row’s city would become Rome.
DELETE removes whole rows. WHERE id = 4 picks Max’s row, and it is gone; the other 3 rows are untouched. Without WHERE, DELETE FROM users would empty the whole table.
Two tables
Orders live in a second table. Each order’s user_id holds the id of the user who placed it: a foreign key, pointing at the users table’s primary key. Every user_id must match the id of a real user.
A JOIN pairs rows from both tables. For each order, the DBMS finds the user whose id equals the order’s user_id: order 1 goes with Ada, 2 with Lina, 3 with Ada again and 4 with Sam.
The answer is one new table: each item beside the name of the user who bought it. Ada’s name is stored once, in users, yet appears on both of her orders. Nothing was copied.
Why a database
Why not just a file? A file has no schema and no keys, and it cannot answer questions: every program must read all of it and search it itself, and two programs saving at once can undo each other.
NoSQL databases store data in other shapes. A document database such as MongoDB can keep Ada and her orders together in one document, read in one go with no JOIN. The price: shared facts, like a name, may be copied in many places.
What happens
;; step 1/11 · tables
the schema of users:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT);
id name city
1 Ada Paris
2 Sam Cairo
3 Lina Oslo
3 rows (one per user) x 3 columns (one per fact)
every value in a column has that column’s type
Without ORDER BY, SQL does not promise any order for the rows of an answer. The JOIN asks for ORDER BY o.id; the figure shows rows in id order. SQLite only checks foreign keys after PRAGMA foreign_keys = ON (they are off by default, for backward compatibility). PostgreSQL and MySQL’s InnoDB check them by default.