MySQL AUTO_INCREMENT

AUTO_INCREMENT tells MySQL to automatically generate a number for a column when a new row is inserted without specifying a value for that column.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

AUTO_INCREMENT is useful for generating unique numeric identifiers.

• Student IDs
• Employee IDs
• Product IDs
• Order IDs
• Payment IDs
• Customer IDs

The common syntax is:

column_name INT AUTO_INCREMENT PRIMARY KEY

Example:

student_id INT AUTO_INCREMENT PRIMARY KEY

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

MySQL automatically generates student_id values when new students are inserted without an ID.

INSERT INTO students
(name, course)
VALUES
('Amit', 'Python');

MySQL automatically generates an ID for the new record.

For a new table using the default starting value, the first generated ID is normally 1.

INSERT INTO students
(name, course)
VALUES
('Amit', 'Python');

INSERT INTO students
(name, course)
VALUES
('Priya', 'Java');

INSERT INTO students
(name, course)
VALUES
('Rahul', 'PHP');

The generated IDs will normally be different, such as 1, 2, and 3.

A common database design is:

student_id INT AUTO_INCREMENT PRIMARY KEY

The PRIMARY KEY ensures uniqueness, while AUTO_INCREMENT automatically generates numeric values.

When using AUTO_INCREMENT, you normally omit the ID column from the INSERT statement.

INSERT INTO students
(name, course)
VALUES
('Neha', 'SQL');

MySQL generates the student_id automatically.

SELECT *
FROM students;

The result can look like:

By default, AUTO_INCREMENT normally starts at 1 for a new table.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

The generated sequence normally begins with 1.

You can set the next AUTO_INCREMENT value using ALTER TABLE.

ALTER TABLE students
AUTO_INCREMENT = 1001;

New automatically generated IDs can then start from 1001 if the table's current state allows that value.

You can explicitly provide an ID, although normally you let MySQL generate it.

INSERT INTO students
(student_id, name, course)
VALUES
(100, 'Amit', 'Python');

The effect on the next generated value depends on the value inserted and the current AUTO_INCREMENT state.

AUTO_INCREMENT values are not guaranteed to be consecutive without gaps.

For example, IDs might be:

1
2
4
5

An ID such as 3 may be missing because a previous row was deleted or an insert consumed a value before being rolled back or otherwise not retained.

Deleting a row does not normally reuse its old AUTO_INCREMENT value.

DELETE FROM students
WHERE student_id = 2;

The next generated ID will normally continue from the current AUTO_INCREMENT sequence rather than filling the deleted ID.

For an InnoDB table, TRUNCATE resets the AUTO_INCREMENT counter.

TRUNCATE TABLE students;

After truncation, a new insert will normally begin again with the initial AUTO_INCREMENT value.

DROP TABLE removes the complete table, including its AUTO_INCREMENT definition.

DROP TABLE students;

If the table is recreated, its AUTO_INCREMENT sequence starts according to the new table definition.

You can inspect the next AUTO_INCREMENT value using SHOW TABLE STATUS.

SHOW TABLE STATUS LIKE 'students';

The Auto_increment column shows the next automatically generated value when available.

AUTO_INCREMENT can be used with integer types such as BIGINT when a larger range of identifiers is required.

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
);

BIGINT provides a much larger numeric range than INT.

AUTO_INCREMENT is intended for an integer numeric column.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

It is not used to automatically generate values for VARCHAR columns.

An AUTO_INCREMENT column can also be a PRIMARY KEY that is referenced by a FOREIGN KEY in another table.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE fees (
    fee_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),

    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

A student management system can use AUTO_INCREMENT for internal database IDs.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100)
);

Each new student receives a generated numeric student_id.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    amount DECIMAL(10,2)
);

INSERT INTO orders
(customer_name, amount)
VALUES
('Amit', 2500.00);

MySQL automatically generates the order_id.

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Each payment receives a unique payment_id automatically.

In PHP using PDO, after inserting a record with an AUTO_INCREMENT primary key, you can retrieve the generated ID using:

$pdo->lastInsertId();

Example:

$stmt = $pdo->prepare(
    "INSERT INTO students (name, course)
     VALUES (?, ?)"
);

$stmt->execute(['Amit', 'Python']);

$student_id = $pdo->lastInsertId();

This is useful when you need the newly created record ID immediately.

INSERT INTO students
(name, course)
VALUES
('Priya', 'Java');

PHP does not need to generate the student_id manually. MySQL generates it.

This reduces the risk of accidentally creating duplicate numeric IDs.

• Trying to use AUTO_INCREMENT on a non-numeric column
• Manually generating IDs unnecessarily
• Assuming IDs will never have gaps
• Assuming deleted IDs will automatically be reused
• Forgetting that TRUNCATE can reset the counter
• Using an ID value that is too large for the selected integer type
• Relying on sequential IDs as business numbers when gaps are unacceptable

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_code VARCHAR(20) UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL
);

INSERT INTO students
(student_code, name, course)
VALUES
('STU001', 'Amit Kumar', 'Python');

INSERT INTO students
(student_code, name, course)
VALUES
('STU002', 'Priya Singh', 'Java');

SELECT *
FROM students;

student_id is automatically generated, while student_code can be a separate business identifier.

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

INSERT INTO orders
(customer_name, amount)
VALUES
('Amit Kumar', 5000.00);

INSERT INTO orders
(customer_name, amount)
VALUES
('Priya Singh', 7500.00);

SELECT *
FROM orders;

Each order receives an automatically generated order_id.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    admission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO students
(student_code, name, course)
VALUES
('STU001', 'Amit Kumar', 'Python');

INSERT INTO students
(student_code, name, course, fee)
VALUES
('STU002', 'Priya Singh', 'Java', 15000.00);

SELECT *
FROM students;

SHOW TABLE STATUS LIKE 'students';

Here, MySQL automatically generates student_id values, while the other constraints provide uniqueness, required values, defaults, and automatic admission timestamps.