⚙️ MySQL AUTO_INCREMENT

MySQL Primary Key AUTO_INCREMENT

? Quick Overview

The AUTO_INCREMENT keyword in MySQL automatically generates a unique number for every new row. It is mainly used with primary key columns so you don’t need to manually assign an ID value.

? Key Concepts

Automatically assigns increasing numbers
Mainly used on PRIMARY KEY columns
Default starting value is 1
Each new row gets the next number
Value can be reset when required

? Syntax / Theory

? View Code Example

-- Basic AUTO_INCREMENT syntax
CREATE TABLE table_name (
  ID INT AUTO_INCREMENT,
  Name VARCHAR(100),
  PRIMARY KEY (ID)
);

? Code Example

? View Code Example

-- Create Employees table with AUTO_INCREMENT
CREATE TABLE Employees (
  ID INT AUTO_INCREMENT,
  Name VARCHAR(100),
  Age INT,
  PRIMARY KEY (ID)
);

? Insert Data

-- Insert records without specifying ID
INSERT INTO Employees (Name, Age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 28);

? Live Output / Explanation

Table Output

ID  Name     Age
1   Alice    25
2   Bob      30
3   Charlie  28

ID values are created automatically
No ID was given while inserting
Next insert will produce ID 4

? Reset AUTO_INCREMENT

? View Code Example

-- Reset next AUTO_INCREMENT value
ALTER TABLE Employees AUTO_INCREMENT = 10;

✅ Tips & Best Practices

Always use AUTO_INCREMENT for unique identifiers
Avoid inserting custom ID values
Don’t reset values frequently in live databases
Deleted values are not reused

? Try It Yourself

Create a Products table with AUTO_INCREMENT
Insert records without ID
Reset AUTO_INCREMENT and test
Observe how IDs keep increasing