Auto Increment - SQL - OneCompiler

The AUTO INCREMENT attribute in SQL is used to automatically generate unique values for a column, typically for a primary key like id.

This is useful when you want the database to handle numbering without manually specifying the value for each new row.

In MySQL, the keyword is AUTO_INCREMENT. In PostgreSQL, you can use SERIAL or GENERATED.

Syntax (MySQL)

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

Example: Auto-increment Employee ID

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50),
  gender VARCHAR(50)
);

INSERT INTO employees (name, age, department, gender)
VALUES ('Alice', 28, 'HR', 'female');

INSERT INTO employees (name, age, department, gender)
VALUES ('Bob', 32, 'IT', 'male');

id will be auto-filled as 1, then 2, and so on.
You don’t need to include the id column in the insert statement.

Key Points

Must be used with a PRIMARY KEY or UNIQUE constraint
Automatically increments by 1 by default
You can override the default starting point using AUTO_INCREMENT = n