MySQL Auto Increment

Automatically generate unique ID numbers

🔢 What is Auto Increment?

AUTO_INCREMENT is a MySQL feature that automatically generates a unique number when a new record is inserted. It's commonly used for primary key columns to ensure each row has a unique identifier without manual input. 


-- Create table with auto increment
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

Result:

Table 'users' created with AUTO_INCREMENT column

Auto Increment Features

🎯

Unique IDs

Generates unique numbers 

id INT AUTO_INCREMENT 
PRIMARY KEY
➕

Automatic

No manual value needed 

INSERT INTO users (username) 
VALUES ('john');
🔄

Sequential

Increments by 1 each time 

-- IDs: 1, 2, 3, 4...
-- Automatically generated
⚙️

Customizable

Set starting value 

ALTER TABLE users 
AUTO_INCREMENT = 1000;

🔹 Creating Auto Increment Columns

Define an AUTO_INCREMENT column when creating a table. The column must be defined as a key, typically PRIMARY KEY. MySQL automatically assigns the next available number when you insert a new row without specifying a value for this column. 

-- Basic auto increment table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

-- Insert without specifying ID
INSERT INTO products (product_name, price) 
VALUES ('Laptop', 999.99);

INSERT INTO products (product_name, price) 
VALUES ('Mouse', 29.99);

Result:

product_id product_name price
1 Laptop 999.99
2 Mouse 29.99

🔹 Setting Starting Value

You can set a custom starting value for AUTO_INCREMENT instead of beginning at 1. This is useful when migrating data, maintaining number ranges, or following specific numbering conventions. Use ALTER TABLE to change the starting value at any time. 

-- Set starting value during creation
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
) AUTO_INCREMENT = 1000;

-- Change starting value on existing table
ALTER TABLE orders AUTO_INCREMENT = 5000;

Result:

Next inserted order will have order_id = 5000

🔹 Getting Last Inserted ID

After inserting a row with AUTO_INCREMENT, you can retrieve the generated ID using LAST_INSERT_ID() function. This is essential when you need to reference the newly created record in subsequent operations or return the ID to your application. 

-- Insert a record
INSERT INTO users (username) 
VALUES ('alice');

-- Get the auto-generated ID
SELECT LAST_INSERT_ID();

-- Use in another insert
INSERT INTO user_profiles (user_id, bio) 
VALUES (LAST_INSERT_ID(), 'Hello World');

Result:

LAST_INSERT_ID()
15

🔹 Adding Auto Increment to Existing Table

You can add AUTO_INCREMENT to an existing column using ALTER TABLE. The column must be defined as a key. This is helpful when modifying database structure or converting manual ID assignment to automatic generation for improved data integrity. 

-- Add auto increment to existing column
ALTER TABLE customers 
MODIFY customer_id INT AUTO_INCREMENT PRIMARY KEY;

-- Or add new auto increment column
ALTER TABLE customers 
ADD id INT AUTO_INCREMENT PRIMARY KEY FIRST;

Result:

Query OK, 0 rows affected

Column modified with AUTO_INCREMENT

🔹 Auto Increment Best Practices

Use AUTO_INCREMENT for primary keys to ensure uniqueness. Avoid manually inserting values into auto-increment columns. Don't reuse deleted IDs as it can cause referential integrity issues. Always use LAST_INSERT_ID() to get generated values rather than querying the maximum ID.

Important Notes:

  • One per table: Only one AUTO_INCREMENT column allowed per table
  • Must be indexed: Column must be defined as a key
  • Integer types only: Works with INT, BIGINT, etc.
  • Gaps are normal: Deleted rows leave gaps in sequence
  • Not reused: Deleted IDs are not automatically reused

🧠 Test Your Knowledge

What happens when you insert a row without specifying an AUTO_INCREMENT column value?