Lesson 3 of 15 · 20 min
Primary keys and identity columns
By the end of this lesson you can choose between a natural and a surrogate key, declare single-column and composite primary keys, and create identity columns with GENERATED ALWAYS AS IDENTITY.
A primary key is the column (or set of columns) whose value identifies one row and only one. Declaring it does two things: PostgreSQL rejects any insert or update that would create a duplicate value, and it makes the key columns NOT NULL. It also builds a unique index behind the scenes, so looking a row up by its key is fast. A table can have only one primary key.
Natural or surrogate?
A natural key is a value that already exists in the business: a product SKU, an IBAN, a national ID, an ISO currency code like SAR. A surrogate key is a number the database invents purely to identify the row: customer_id = 7 means nothing outside the system.
Natural keys look attractive because they carry meaning, but they have to be truly unique, always known and never changing. Emails change, people share names, products get renumbered, and many customers share a city. Surrogate keys meet all three conditions by construction, which is why most operational tables (including every dataset in this course) use them. Natural values still matter: keep them as ordinary columns and protect them with a UNIQUE constraint (lesson 5).
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons