We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
A column (or combination of columns) that uniquely identifies each row in a database table. Must be unique and not null.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Primary Key across a run of lessons, not one page. These 4 alone are about 210 minutes of step-by-step reading, 2 of them with code you run in the browser, all with a quiz.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
What does a catalog like Unity Catalog do that a folder cannot? I put 3,475,226 NYC taxi trips behind three real catalogs: an Iceberg SQL catalog, the Apache Iceberg REST catalog and Unity Catalog OSS 0.6.0. A rename sent one SQL UPDATE and moved 0 of 9 files, while moving the folder broke a reader after 1 of 10 files. A writer that crashed before its swap left a newest metadata file with 123,646 trips nobody committed. Two catalogs on one folder both accepted writes and disagreed. Unity Catalog refused a user with no grants, but the same user read every row straight from the folder.
Renaming one category rewrote 200,000 rows in a flat table and 1 in a normalised one. A partial update left the flat table holding two answers to one question, and the join it costs measured 1.34 times.
Set up an active-passive PostgreSQL database: a primary and a streaming standby, slots, synchronous_commit, hot standby and fencing, run in a real lab.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with ACID on a Lakehouse: Two Writers, One Table, Who Winspay once,
yours for life
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.
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 also checked at write time, before the row lands, as a check on the row itself rather than a lookup in the index.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Primary Key in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
