AUTO_INCREMENT behavior in MySQL

Introduction

AUTO_INCREMENT helps automatically create unique numbers for new rows, so you don't have to type them yourself.

When you want each new record to have a unique ID without typing it.

When adding new users to a system and each user needs a unique number.

When tracking orders and each order needs a unique order number.

When creating a list of items where each item needs a unique code.

Syntax

MySQL

CREATE TABLE table_name (
  id INT AUTO_INCREMENT PRIMARY KEY,
  column_name datatype
);

AUTO_INCREMENT works only on integer columns.

Usually, the AUTO_INCREMENT column is the PRIMARY KEY.

Examples

Creates a users table where user_id automatically increases for each new user.

MySQL

CREATE TABLE users (
  user_id INT AUTO_INCREMENT,
  username VARCHAR(50),
  PRIMARY KEY (user_id)
);

Inserts two users. user_id will be 1 for Alice and 2 for Bob automatically.

MySQL

INSERT INTO users (username) VALUES ('Alice');
INSERT INTO users (username) VALUES ('Bob');

Creates orders table with order_id auto-increasing and set as primary key.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100)
);

Sample Program

This creates a products table with an auto-incrementing product_id. Then it adds three products. Finally, it shows all products with their IDs.

MySQL

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(50)
);

INSERT INTO products (product_name) VALUES ('Pen');
INSERT INTO products (product_name) VALUES ('Notebook');
INSERT INTO products (product_name) VALUES ('Eraser');

SELECT * FROM products;

Important Notes

If you delete rows, AUTO_INCREMENT does not reuse old numbers by default.

You can reset AUTO_INCREMENT value using ALTER TABLE if needed.

Only one AUTO_INCREMENT column is allowed per table.

Summary

AUTO_INCREMENT automatically creates unique numbers for new rows.

It is used on integer columns, usually as the primary key.

It helps avoid manually typing unique IDs for each new record.