What is AUTO_INCREMENT and how does it work?

Question

Accepted Answer

13
   What is AUTO_INCREMENT and how does it work?
  Level: Basic — Tests your understanding of how MySQL generates unique IDs automatically.

  AUTO_INCREMENT is a column attribute that automatically generates a unique, sequential number for each new row inserted. It is most commonly used on a Primary Key column so you don't have to manually assign IDs.

  By default, the sequence starts at 1 and increases by 1 for every new record. When you insert a row, you can leave the AUTO_INCREMENT column out (or pass NULL/DEFAULT) and MySQL fills in the next value automatically.

  
     Key rules: A table can have only one AUTO_INCREMENT column, it must be indexed (usually a PRIMARY KEY or UNIQUE key), and it should be of an integer type.
  

   Important Behaviours
  
     You can set a custom starting value with AUTO_INCREMENT = n.
     The step size is controlled by the auto_increment_increment system variable.
     TRUNCATE resets the counter; DELETE does not.
     Deleted values are not reused — gaps can appear in the sequence.
     Use LAST_INSERT_ID() to fetch the most recently generated value.
  

   Common Gotcha — Gaps in the Sequence
  
    
       Myth
      
        IDs are always perfectly continuous (1,2,3...).
        Deleting a row frees its ID for reuse.
      
    
    
       Reality
      
        Failed inserts and rollbacks can leave gaps.
        Deleted IDs are never reused automatically.
      
    
  

   Quick Example
-- Define an AUTO_INCREMENT primary key
CREATE TABLE users (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50)
) AUTO_INCREMENT = 100;   -- start counting from 100

-- Omit id: MySQL assigns it automatically
INSERT INTO users (name) VALUES ('Rumman');  -- id = 100
INSERT INTO users (name) VALUES ('Ansari');  -- id = 101

-- Get the last generated ID
SELECT LAST_INSERT_ID();   -- returns 101

-- Reset the counter (only works if table is empty / higher than max)
ALTER TABLE users AUTO_INCREMENT = 500;

  
     Interviewer tip: The one-liner they want — "AUTO_INCREMENT automatically generates a unique sequential integer for each new row; a table can have only one such column and it must be indexed."

What is AUTO_INCREMENT and how does it work?

Answer with Explanation

What is AUTO_INCREMENT and how does it work?

Important Behaviours

Common Gotcha — Gaps in the Sequence

Myth

Reality

Quick Example