SQL Auto Increment

Auto-increment lets the database automatically generate a unique number for each new row, which is ideal for primary keys.

Why Auto-Increment Exists

Most tables need a column that uniquely identifies each row, and that identifier usually has no business meaning of its own. Instead of asking the application to invent and track these numbers, you can let the database hand out a fresh value automatically every time a row is inserted. The database remembers the last number it used and gives out the next one, so each row is guaranteed a distinct identifier without any extra effort from you.

The idea is standard across databases, but the exact keyword is not. MySQL uses AUTO_INCREMENT, SQL Server uses IDENTITY, and PostgreSQL and other systems have their own equivalents. The examples below cover the most common dialects.

MySQL: AUTO_INCREMENT

In MySQL you add the AUTO_INCREMENT keyword to an integer primary key. By default the first row gets the value 1 and each following row increases by 1.

AUTO_INCREMENT in MySQL

CREATE TABLE Employees (
  EmployeeID INT NOT NULL AUTO_INCREMENT,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  PRIMARY KEY (EmployeeID)
);

INSERT INTO Employees (FirstName, LastName)
VALUES ('Asha', 'Menon');
Note: You do not list the auto-increment column in the INSERT. Because you leave it out, the database fills it in for you. To make numbering start at a value other than 1 in MySQL, use ALTER TABLE Employees AUTO_INCREMENT = 100;

SQL Server: IDENTITY

SQL Server uses IDENTITY(seed, increment), where the seed is the first value and the increment is how much to add each time. IDENTITY(1,1) starts at 1 and increases by 1, matching MySQL's default behavior.

IDENTITY in SQL Server

CREATE TABLE Employees (
  EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50)
);

INSERT INTO Employees (FirstName, LastName)
VALUES ('Asha', 'Menon');

Other Dialects

DatabaseHow to auto-generate the key
MySQLcolumn INT AUTO_INCREMENT
SQL Servercolumn INT IDENTITY(1,1)
PostgreSQLcolumn SERIAL, or column INT GENERATED ALWAYS AS IDENTITY
Oraclecolumn GENERATED ALWAYS AS IDENTITY, or a SEQUENCE
SQLitecolumn INTEGER PRIMARY KEY AUTOINCREMENT

Auto-generated key in PostgreSQL

CREATE TABLE Employees (
  EmployeeID INT GENERATED ALWAYS AS IDENTITY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  PRIMARY KEY (EmployeeID)
);
Note: Auto-increment values are not guaranteed to be gapless. If an insert fails or a row is deleted, the skipped numbers are usually not reused. Treat the value as a stable unique identifier, not as a running count of rows.

Good Practices

  • Use an auto-increment column as a surrogate primary key when no natural key exists.
  • Never insert or update the value manually unless you have a specific reason to.
  • Choose an integer type large enough for the number of rows you expect over the table's lifetime, such as BIGINT for very large tables.
  • Do not rely on the numbers being consecutive or on their exact order matching insertion time under heavy concurrency.

Exercise: SQL Auto Increment

What is the main purpose of an auto-increment column?

Keep Going

SQL Interview QuestionsPractice real questions asked for SQLDatabase Administrator SalarySee what Database Administrator roles pay in the marketAuto Apply for JobsApply to open roles related to SQL and tweak your resume