Primary Key
A column (or combination of columns) that uniquely identifies each row in a database table. Must be unique and not null.
What is Primary Key?
In short
A primary key is the column or set of columns that uniquely identifies every row in a database table. It cannot be null and no two rows can share the same value, which gives each record one stable, guaranteed-unique handle the database and your application can use to find, link, and update it.
What a primary key actually is
Every row in a relational table needs a way to be told apart from every other row. The primary key is the column you nominate for that job. Pick a users table: two people can have the same name, the same city, even the same signup date, but the id column gives each person exactly one value that belongs to no one else.
A primary key has two hard rules. It must be unique, so the same value never appears twice. And it must be not null, because a row with no key value cannot be identified. A key can also be made of more than one column, called a composite key. An order_items table might use the pair order_id plus product_id as its key, because neither column is unique on its own but the combination is.
A table can have at most one primary key. You can enforce uniqueness on other columns too, like an email field, using a UNIQUE constraint, but only one of those constraints gets the title of primary key and becomes the table's main identity.
How the database enforces it under the hood
When you declare a primary key, the database builds an index on those columns automatically. In PostgreSQL and most engines this is a B-tree index, a sorted tree structure that lets the engine find any row by its key in a handful of steps instead of scanning the whole table. So a lookup by primary key on a table with 50 million rows is still nearly instant.
That same index is what enforces the uniqueness rule. Before any INSERT or UPDATE commits, the engine checks the index to see if the value already exists. If it does, the write is rejected with a duplicate key error. The not-null rule is checked the same way, at write time, before the row lands.
In InnoDB, the MySQL storage engine, the primary key goes one step further. The table is physically stored sorted by the primary key, which is called a clustered index. The actual row data lives in the leaf nodes of that key's tree. This is why range scans on the primary key are fast in MySQL, and why every secondary index quietly stores a copy of the primary key to find the full row.
Choosing a key and the trade-offs
The big choice is natural key versus surrogate key. A natural key uses real data that is already unique, like a country code or an ISBN. A surrogate key is a meaningless value the system generates just to be the identifier, like an auto-incrementing integer or a UUID. Most teams default to surrogate keys because real-world data has a habit of changing, and changing a primary key is painful since every table that references it must change too.
Auto-incrementing integers are compact, fast, and sort in insertion order, which keeps the clustered index tidy. The downside is they leak information, a competitor can guess your order count, and they do not work well when you need to generate IDs across many servers without coordination.
UUIDs solve the distributed problem because any machine can generate one with effectively zero chance of collision, but a random UUID scatters writes all over the index and bloats it at 16 bytes versus 8 for a bigint. Time-ordered variants like UUIDv7 and Twitter's Snowflake IDs were designed to keep the global uniqueness while inserting in roughly sorted order, getting most of the index benefit back.
A concrete example
Picture an e-commerce schema. The orders table has order_id as its primary key, a bigint that auto-increments. The customers table has customer_id as its key. To link an order to a buyer, orders carries a customer_id column that is a foreign key pointing at the customers primary key.
That link only works because the primary key is guaranteed unique and present. When the app loads an order page, it runs SELECT star FROM orders WHERE order_id = 80421, and the engine jumps straight to that one row through the primary key index. When it needs the buyer, it follows customer_id to exactly one customer row.
Without a primary key, none of this holds together. You could not reliably update one order without risking touching another, foreign keys would have nothing stable to reference, and a single duplicated or missing identifier could corrupt every relationship built on top of it.
Where it is used in production
PostgreSQL
Declaring PRIMARY KEY creates a unique B-tree index and a NOT NULL constraint automatically; commonly paired with a bigint GENERATED ALWAYS AS IDENTITY column.
MySQL InnoDB
Stores the entire table as a clustered index ordered by the primary key, so the key choice directly affects on-disk layout and scan speed.
Amazon DynamoDB
Every item must have a primary key, either a single partition key or a partition key plus sort key, and that key decides which physical partition stores the item.
MongoDB
Every document gets a mandatory _id primary key; if you do not supply one, the server generates a 12-byte ObjectId that encodes a timestamp.
Frequently asked questions
- What is the difference between a primary key and a unique key?
- Both enforce uniqueness, but a table can have only one primary key and it can never be null, while you can have many unique constraints and they usually allow one null value. The primary key is the table's official identity and is what foreign keys point to.
- Can a primary key be made of more than one column?
- Yes, that is called a composite primary key. It is common in join tables, for example a primary key of student_id plus course_id in an enrollments table, where each column repeats but the pair is always unique.
- Should I use an auto-increment integer or a UUID for my primary key?
- Use an auto-increment bigint when all writes go through one database, it is smaller and keeps the index sorted. Use a UUID when many machines need to generate IDs independently or you want to hide row counts; prefer a time-ordered form like UUIDv7 to avoid scattered index writes.
- Can a primary key value be null?
- No. The not-null rule is part of the definition, because a row with no key value could not be uniquely identified. The database rejects any insert or update that would leave a primary key column empty.
- Can I change a primary key value after a row is created?
- You can, but it is risky. Any foreign keys referencing that value must be updated too or the references break, which is why teams favor surrogate keys made of meaningless generated values that never need to change.
Learn Primary Key hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
See also
Related glossary terms you might want to look up next.
Foreign Key
A column in one table that references the primary key of another table, enforcing referential integrity between related tables.
Index
A data structure that speeds up database lookups. Like the index at the back of a book that lets you jump to the right page instead of reading every page.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).