AUTO_INCREMENT / IDENTITY

Hook: Interviewers love this because it looks simple, but it reveals whether you know how databases create safe unique IDs under concurrency.

Question: What are AUTO_INCREMENT and IDENTITY in SQL?

Answer: They are database features that generate a new numeric value for a column when you insert a row. The usual purpose is to create a surrogate key, which is an artificial ID with no business meaning, such as an order id or user id. The important idea is that the database, not your application, safely picks the next value.

Interview-Ready Answer: AUTO_INCREMENT and IDENTITY are automatic number generators for columns, usually primary keys. I use them when I want the database to assign a unique id on insert, which avoids race conditions from application code doing MAX(id)+1. One useful detail is that these values are not guaranteed to be gapless, because rollbacks, deletes, and caching can leave holes.

What it is

Detailed Explanation: The core idea is very small: one column is marked as "auto-generated," and the database fills it in during INSERT if you do not provide a value. In practice, this is used for a primary key, but technically it can be a unique surrogate key even if it is not the primary key.

How it works under the hood

1. The table stores metadata that says, "this column gets its next value from the engine."
2. When a row is inserted without that column, the engine asks its generator for the next number.
3. The generator returns a value atomically, which means the step happens as one indivisible action, so two concurrent inserts do not get the same id.
4. The row is written with that value, and the unique or primary-key index prevents duplicates.
5. If the transaction rolls back, the number is usually not reused, so gaps are normal.

Think of it like a coat-check ticket dispenser: every time someone arrives, the machine hands out the next ticket number. If someone leaves the line or the machine prints a ticket that is never used, that number is still gone.

AUTO_INCREMENT vs IDENTITY vs SEQUENCE

In simple words, the behavior is the same idea across engines, but the syntax and rules differ. IDENTITY is the SQL-standard-style concept, while AUTO_INCREMENT is the MySQL name for it. PostgreSQL also supports identity columns, and older PostgreSQL code often used SERIAL, which is a convenience shortcut built on a sequence.

When and why to use it

• Use it for internal row ids that should be unique and easy to join on.
• Use it when your app should not guess the next number.
• Use it when you want small, fast integer keys instead of long text keys.
• Avoid it if you need a globally unique id across many systems without central coordination; then UUID or a distributed ID scheme is often better.

Performance notes: assigning the next value is typically O(1), because the engine just grabs the next number from its generator. The insert still pays the normal cost of writing the row and updating indexes, so the key generation itself is not the expensive part. Identity columns are also compact: INT uses 4 bytes and BIGINT uses 8 bytes per row, which is much smaller than a 36-character UUID.

Important edge cases

• Gaps are normal. Deletes do not usually reuse ids, and rollbacks can burn numbers.
• Not gapless. If someone asks for "no missing numbers," identity is the wrong tool.
• Overflow matters. An INT maxes out at about 2.1 billion; a busy system should often use BIGINT.
• Replication and sharding can need extra planning, because multiple writers can collide unless the system is designed for it.
• Manual inserts may be allowed or blocked depending on the dialect and whether the column is BY DEFAULT or ALWAYS.

Memory rule: if the app is asking, "What number should I use next?" the app is doing the database’s job.

Real-World Example: Imagine a checkout service for an online store. Every order needs an internal order id, and the team uses an identity column so the database assigns the id safely even when hundreds of customers check out at the same second.

What goes wrong when people misunderstand this? A developer replaces the identity column with application code that does MAX(order_id) + 1 before inserting. Under load, two requests read the same maximum value, both try to insert the same next id, and one fails with a duplicate-key error. Users see failed checkouts, logs fill with unique constraint violations, and support tickets say "payment succeeded but order confirmation never appeared."

A related bug happens when a team expects ids to be continuous. They see order 1042 and then 1045, assume orders were lost, and start a fake incident. In reality, 1043 and 1044 were reserved and then rolled back during failed transactions. The lesson is that identity numbers are for uniqueness, not for counting business events.

Follow-up & Tricky Questions:

• Can identity values have gaps? Yes. Rollbacks, failed inserts, deletes, and cached values can all leave holes, so you should never promise continuous numbering.
• What is the difference between GENERATED BY DEFAULT and GENERATED ALWAYS? BY DEFAULT lets you supply your own value; ALWAYS blocks manual values unless you explicitly override them. That makes ALWAYS safer when the database must control the id.
• Does deleting a row reuse its id? Usually no. Reuse can create confusing history and is avoided because the key’s job is uniqueness, not compactness.
• Should I use INT or BIGINT? Use BIGINT if you expect long life or high volume. A 4-byte INT tops out around 2.1 billion, which can be too small for large systems.
• Is identity the same as a primary key? No. Identity is how a value is generated; primary key is the constraint that makes it unique and non-null. Many designs use both together, but they are different features.
• Can I insert an explicit value into an identity column? Sometimes. Whether it is allowed depends on the dialect and on whether the column is defined as BY DEFAULT or ALWAYS.
• Does rollback reuse the number? Usually not. The number is often consumed when the insert is attempted, so a rollback leaves a gap rather than giving the id back.
• Is AUTO_INCREMENT standard SQL? No. It is MySQL-specific wording; IDENTITY is the more standard term.
• Can I depend on ids being in insert order? Not perfectly. Concurrent transactions, rollbacks, and replication can make the final visible order differ from the order you imagined.

Common Mistakes:

• Using MAX(id)+1 in application code. Fix: let the database generate the value so concurrent requests do not collide.
• Assuming ids are gapless. Fix: treat them as unique labels, not as a perfect sequence.
• Using INT for a table that will grow forever. Fix: choose BIGINT early if the row count may become huge.
• Confusing generation with constraint. Fix: remember that identity creates numbers, while primary key/unique enforces rules on them.

Memory Hook: Think of identity like a deli ticket machine: the store hands out the next number, not the customer. The number is for service order, not for meaning, and some tickets are skipped when people leave or transactions fail.

Cheat Sheet:

• AUTO_INCREMENT and IDENTITY both mean auto-generated ids.
• Use them for surrogate keys, usually with a primary key.
• They are fast; generating the next number is usually O(1).
• They are not gapless and not safe to infer counts from.
• BY DEFAULT allows manual ids; ALWAYS blocks them unless overridden.
• Choose BIGINT when long-term scale matters.

Practice Tasks:

• Create a table with an identity column and insert rows without specifying the id.
• Change the column from BY DEFAULT to ALWAYS and test what manual inserts do.
• Simulate a rollback and observe that the next generated id can skip a value.