AUTO_INCREMENT behavior in SQL

Introduction

AUTO_INCREMENT helps automatically give a unique number to each new row, so you don't have to type it yourself.

When you want to create a list of users and each user needs a unique ID.

When adding new products to a store and each product needs a unique code.

When recording orders and each order needs a unique number.

When you want to keep track of entries without repeating numbers.

When you want the database to handle numbering automatically.

Syntax

SQL

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

AUTO_INCREMENT is usually used with integer columns.

The column with AUTO_INCREMENT must be a key (usually PRIMARY KEY).

Examples

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

SQL

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

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

SQL

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

Creates orders table with order_number auto-incrementing as the primary key.

SQL

CREATE TABLE orders (
  order_number INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE
);

Sample Program

This creates a books table with an auto-incrementing book_id. Then it adds three books and shows all rows.

SQL

CREATE TABLE books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100)
);

INSERT INTO books (title) VALUES ('Book A');
INSERT INTO books (title) VALUES ('Book B');
INSERT INTO books (title) VALUES ('Book C');

SELECT * FROM books;

Important Notes

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

You can reset AUTO_INCREMENT value if needed, but usually it's best to let it increase.

AUTO_INCREMENT works differently in some SQL databases; this example is standard for MySQL.

Summary

AUTO_INCREMENT automatically gives a unique number to new rows.

It is used for primary keys to keep data unique and easy to find.

You don't need to type the number; the database does it for you.