SQL Auto Increment - SQL Tutorial

Auto-increment allows a unique number to be generated automatically when a new record is inserted. This is commonly used for primary keys — ensuring each record gets a unique identifier without manual input.

SQL Auto Increment Syntax

In SQLite, you declare a column as INTEGER PRIMARY KEY AUTOINCREMENT. In our playground, AUTOINCREMENT works only with the column declared as INTEGER PRIMARY KEY.

CREATE TABLE Example (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

SQL Auto Increment Syntax for MySQL

CREATE TABLE Persons (
  Personid INT NOT NULL AUTO_INCREMENT,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  PRIMARY KEY (Personid)
);

Note: This syntax does not work in SQLite — AUTO_INCREMENT is specific to MySQL.

SQL Auto Increment Syntax for SQL Server

CREATE TABLE Persons (
  Personid INT IDENTITY(1,1) PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);

IDENTITY(1,1) means: start at 1 and increment by 1.

Note: IDENTITY is not valid in SQLite.

SQL Auto Increment Syntax for Access

CREATE TABLE Persons (
  Personid AUTOINCREMENT PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);

Note: AUTOINCREMENT is specific to Microsoft Access and not supported in SQLite.

SQL Auto Increment Syntax for Oracle

In Oracle, auto-increment is achieved using a SEQUENCE:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

Then use the sequence when inserting:

INSERT INTO Persons (Personid, FirstName, LastName)
VALUES (seq_person.NEXTVAL, 'Lars', 'Monsen');

Note: Oracle sequences are not supported in SQLite.

SQL Auto Increment Example

This query creates a table using SQLite’s auto-increment format:

CREATE TABLE TestPatients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

To insert a new patient:

INSERT INTO TestPatients (name)
VALUES ('Maria Santos');

This query will insert Maria Santos and assign a unique id automatically.

You can confirm with:

SELECT * FROM TestPatients;