Primary Key

Hook: Interviewers love this one because a primary key is the table's identity card: if the identity is weak, every join, update, and lookup becomes risky.

Question: What is a primary key in SQL?

Answer: A primary key is the column or set of columns that uniquely identifies each row in a table. Its values must be unique and cannot be NULL, so every row has one clear identity. A table can have only one primary key constraint, but that key can be made from multiple columns.

Interview-Ready Answer: In SQL, a primary key is the main identifier for a row. I use it when I need each row to be uniquely and reliably found, and I know it cannot be null or duplicated. A useful detail is that most databases back it with a unique index, so lookups and joins on the key are fast.

What a primary key really means

Detailed Explanation: Think of a primary key as the row's official name tag. It is not just a column with unique values; it is a rule enforced by the database that says, 'this value, or this combination of values, must point to exactly one row and never be missing.' That is why the database uses it for integrity, joins, and fast access.

How it works under the hood

1. When you define a primary key, the database records a constraint on one column or on a group of columns.
2. Before the table is accepted, the database checks the existing data to make sure there are no duplicates and no NULL values in the key columns.
3. Most database systems create a unique index behind the scenes. An index is a data structure that helps the database find rows quickly instead of scanning the whole table.
4. On every INSERT or key-changing UPDATE, the database checks the index first. If the key already exists, the statement fails.
5. Foreign keys often point to primary keys because the primary key is stable, unique, and guaranteed to identify one parent row.
6. The optimizer can use the primary key index to speed up joins, point lookups, and existence checks.

When and why to use it

• Use a primary key when a row must have a single, reliable identity.
• Use it for parent tables such as customers, orders, or products.
• Use it as the target for child tables that need a stable reference.
• Choose a value that rarely changes. Changing a primary key is usually painful because related rows may depend on it.

Primary key vs unique constraint

A lot of candidates mix these up. Both enforce uniqueness, but they are not the same job. A primary key is the main identity of the row. A unique constraint is an extra rule that says 'this other value must also stay unique.'

Performance and edge cases

Because the primary key is usually backed by an index, lookup is typically O(log n) rather than a full table scan. In practice, a B-tree index on a table with millions of rows often has only a few levels, so the database may find the row in about 3 to 4 page reads. Inserts and updates are a little more expensive than a heap with no index, because the index must also be maintained.

There are a few real gotchas:

• Wide keys are expensive: a VARCHAR email key takes more space than an integer key, so every index and child table becomes heavier.
• Mutable keys are risky: if you use email or phone number as the primary key and it changes, every related row may need to change too.
• Composite keys are valid: a table can have one primary key constraint made from multiple columns, such as (order_id, line_no).
• One table, one PK: you cannot have two primary key constraints on the same table, but you can have multiple UNIQUE constraints.
• Physical storage differs: in some systems, like MySQL InnoDB, the primary key is clustered, meaning rows are stored in primary-key order; in PostgreSQL, the primary key creates a unique index, but rows are not clustered by default.

Memory check: 'Primary key = the row's passport number: one per row, never blank, never shared.'

Real-World Story: Imagine a checkout service for an online store. The orders table should use order_id as the primary key, because one customer can place many orders. A team once used customer_id instead, which meant the second order from the same customer failed with a duplicate-key error or got overwritten by an upsert. Users saw missing orders, support saw duplicate-key logs, and warehouse staff almost shipped the wrong items because the system could no longer tell orders apart. The fix was simple but important: keep order_id as the primary key, and keep customer_id as a normal foreign key that points back to customers.

Follow-up & Tricky Questions:

• Can a primary key be composite? Yes. A composite primary key uses two or more columns together to identify one row, which is common in bridge tables like order lines or enrollment tables.
• Can a primary key contain NULL? No. If a value is missing, the database cannot use it to identify a row, so primary key columns are always not null.
• What is the difference between a primary key and a unique key? A primary key is the table's main identity, while a unique constraint enforces an extra unique rule. A table can have many unique constraints but only one primary key.
• Should I use a natural key or a surrogate key? A natural key comes from real business data, like an email or SKU, while a surrogate key is a made-up identifier like an integer id. In many systems, surrogate keys are safer because business data can change.
• Can you change a primary key later? Technically yes in many databases, but it is often expensive and risky because child tables may reference it. Good schema design tries to keep primary keys stable from the start.
• Tricky: does primary key always mean auto-increment? No. Auto-increment, identity, or sequence generation is separate from the primary key rule; the key can be manually assigned too.
• Tricky: does every database store rows physically in primary-key order? No. Some engines cluster data by the primary key, but others only create an index and leave row storage separate.

Common Mistakes:

• Thinking a primary key auto-generates values: correction: it only enforces identity; generation is a separate feature such as identity or sequence.
• Choosing a value that changes often: correction: pick a stable identifier, because changing a primary key can ripple through child tables.
• Using a wide text field as the key: correction: keep keys small when possible, because every index and join becomes cheaper with fewer bytes.
• Confusing primary key with unique constraint: correction: unique means 'no duplicates'; primary key means 'the official row identity' and also forbids nulls.

Memory Hook: Primary key = a table's passport number: one per row, never blank, never shared.

Cheat Sheet:

• Uniquely identifies each row.
• Cannot be NULL.
• Only one primary key per table, but it may be composite.
• Usually backed by a unique index.
• Great target for foreign keys and fast joins.
• Keep it stable and as small as practical.

Practice Tasks:

• Create a customers table with an integer primary key and insert three rows.
• Add a second table with a composite primary key, like (invoice_id, line_no), and try inserting a duplicate row.
• Compare a primary key and a unique constraint by writing both on different columns and checking which one allows NULL in your DBMS.