When an app stores users and their writing, it needs a reliable way to tell one row from another. A primary key is a column—or a set of columns—whose values uniquely identify each row in a table. section 8.2.2 section 6.1.2
For a users table, username can serve as the primary key: every user must have one, and no two users can share it. For a haikus table, haiku_id identifies each haiku instead. Its SERIAL data type makes PostgreSQL assign successive integer values as rows are added. section 8.2.2 section 8.2.4
CREATE TABLE haikus ( haiku_id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, author VARCHAR(100), text TEXT, FOREIGN KEY (author) REFERENCES users(username) );
In the haikus table definition, haiku_id is the primary key. The author column has a different job: it is a foreign key that refers to an existing username in the users table. section 8.2.4 section 8.2.2
Find the row you mean
If two haikus have different haiku_id values, an update can target one with WHERE haiku_id = 1, while a delete can target the other with WHERE haiku_id = 2. The key gives each operation a specific row to identify. section 8.2.4
A key need not be a single column. In a sales CSV, a row is identified by the combination of date, product name, segment, category, gender, age group, and state. Those columns together form its primary key; the sales figures describe the identified row. section 6.1.2
