Skip to main content

What is auto-increment (AUTO_INCREMENT / SERIAL)?

SQLJunior~1 min read
Suggest an edit

Auto-increment (AUTO_INCREMENT in MySQL / SERIAL in PostgreSQL) is a field property where the value automatically increases by 1 as each new record is added.

The main points

  1. Used for primary keys, so every record gets a unique identifier with no manual entry.
  2. Automatic increase: when a new record is inserted, the DBMS assigns the next available number itself.
  3. A MySQL example:
sql
CREATE TABLE Employees ( ID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(50) NOT NULL ); INSERT INTO Employees (Name) VALUES ('Ivan'); INSERT INTO Employees (Name) VALUES ('Maria'); -- ID will automatically be 1 and 2
  1. A PostgreSQL example:
sql
CREATE TABLE Employees ( ID SERIAL PRIMARY KEY, Name VARCHAR(50) NOT NULL );
  1. Notes:
  • It can start from any number (e.g. AUTO_INCREMENT=100).
  • The value doesn't decrease when records are deleted, the next number keeps increasing.

Put simply, auto-increment is a way to automatically number records, so every one is unique.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit