MySQL — Auto Increment

Basic Auto Increment

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Inserting with Auto Increment

-- Let MySQL assign the ID
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- Explicitly set ID (not recommended)
INSERT INTO users (id, name) VALUES (100, 'Charlie');

Setting AUTO_INCREMENT Start

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
) AUTO_INCREMENT = 1000;

Altering AUTO_INCREMENT

ALTER TABLE users AUTO_INCREMENT = 5000;

Viewing Current Value

SHOW TABLE STATUS LIKE 'users';

Auto Increment Rules

Rule	Detail
Column type	Must be INT, BIGINT, etc.
Key	Must be part of an index
One per table	Only one AUTO_INCREMENT column
Starts at 1	By default
Gap-free	Gaps can occur with DELETE

Resetting AUTO_INCREMENT

-- Reset to max + 1
ALTER TABLE users AUTO_INCREMENT = (
    SELECT MAX(id) + 1 FROM users
);

Composite Keys

CREATE TABLE enrollments (
    student_id INT,
    course_id INT AUTO_INCREMENT,
    PRIMARY KEY (student_id, course_id)
);

Mini Practice

Write SQL code that:

1. Creates a table with AUTO_INCREMENT
2. Inserts rows and observe auto-generated IDs
3. Changes the AUTO_INCREMENT start value
4. Shows the current AUTO_INCREMENT value

Up Next

Continue with Stored Procedures — reusable SQL code blocks.