SQL AUTO INCREMENT

What is SQL AUTO_INCREMENT?

AUTO_INCREMENT automatically generates a unique numeric value for each new row inserted into a column, usually a primary key.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL AUTO INCREMENT: Native: MySQL, MariaDB. Equivalent concept: PostgreSQL (SERIAL/IDENTITY), SQL Server (IDENTITY), SQLite (INTEGER PRIMARY KEY AUTOINCREMENT), Oracle (IDENTITY). Not in standard SQL keyword list.

SQL AUTO INCREMENT Full Explanation

AUTO_INCREMENT is a column attribute, primarily used in MySQL and MariaDB, that tells the database engine to assign the next available integer to the column whenever a new row is inserted. The counter starts at 1 (unless overridden) and increases by 1 with each insert. The generated value is guaranteed to be unique within the table and is returned to the client so it can be referenced immediately.Behind the scenes the engine maintains an internal counter, persisted even after restarts. You can retrieve the most recent value with LAST_INSERT_ID().Caveats:- Only one AUTO_INCREMENT column is allowed per table and it must be indexed (commonly PRIMARY KEY).- Gaps appear if a transaction rolls back after reserving a number.- The attribute works only on integer types (TINYINT, SMALLINT, INT, BIGINT) and cannot be combined with NULL default.- In replication, improper handling of offsets can cause collisions.Other dialects implement the same idea differently: PostgreSQL uses SERIAL or IDENTITY, SQL Server uses IDENTITY, and Oracle uses IDENTITY or sequences with triggers.

SQL AUTO INCREMENT Syntax

-- Create a table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

-- Set custom starting point
ALTER TABLE users AUTO_INCREMENT = 1000;

SQL AUTO INCREMENT Parameters

Example Queries Using SQL AUTO INCREMENT

-- Insert rows; id auto populates
INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

-- Retrieve last generated id
SELECT LAST_INSERT_ID();

Expected Output Using SQL AUTO INCREMENT

Each insert adds a row where id is automatically set (1 for Alice, 2 for Bob)
LAST_INSERT_ID() returns the id of the most recent successful insert in the current session

Use Cases with SQL AUTO INCREMENT

Define surrogate primary keys without managing sequences
Quickly reference newly inserted rows in application code
Ensure uniqueness when bulk-loading data without specifying ids

Common Mistakes with SQL AUTO INCREMENT

Adding two AUTO_INCREMENT columns in one table
Using AUTO_INCREMENT on a non-indexed column
Expecting consecutive numbers with no gaps after rollbacks or deletes
Mixing manual id values that collide with the counter

First Introduced In

MySQL 3.23 (1998)

Frequently Asked Questions

What does AUTO_INCREMENT do?

It auto-generates a unique integer for the column on every insert, removing the need to specify the value manually.

How many AUTO_INCREMENT columns can a table have?

Exactly one. It must be indexed and is typically declared as the primary key.

How can I reset or change the AUTO_INCREMENT starting number?

Use ALTER TABLE table_name AUTO_INCREMENT = new_start_value; The next insert will use this value.

Will AUTO_INCREMENT numbers ever repeat?

No, unless the counter is manually reset to a previous value or it overflows the data type size.