Core Code
Core Code Modern Dev Handbook

SQL Auto Increment

AUTO_INCREMENT automatically generates a unique number for a column, commonly used for primary keys in MySQL/MariaDB.
On this page

SQL AUTO_INCREMENT

AUTO_INCREMENT is a MySQL/MariaDB feature that automatically generates a unique number for a column. It is commonly used for primary key columns.

AUTO_INCREMENT in CREATE TABLE

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150)
);

Insert Without Providing the id

When a column is AUTO_INCREMENT, you typically do not include it in INSERT statements.

INSERT INTO customers (name, email)
VALUES ('John Smith', 'john@example.com');

The database will generate the id automatically.

Get the Last Inserted id

In MySQL/MariaDB you can retrieve the last generated AUTO_INCREMENT value:

SELECT LAST_INSERT_ID();

Set the Starting Value

You can set the initial AUTO_INCREMENT value for a table.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE NOT NULL
) AUTO_INCREMENT = 1000;

Change AUTO_INCREMENT Value

You can change the next AUTO_INCREMENT value using ALTER TABLE.

ALTER TABLE orders AUTO_INCREMENT = 5000;

Reset AUTO_INCREMENT

If you want to remove all rows and reset the counter, TRUNCATE TABLE is often used:

TRUNCATE TABLE orders;

Important Notes

  • AUTO_INCREMENT values are not guaranteed to be gap-free (deleted rows leave gaps)
  • Inserts that fail may still consume AUTO_INCREMENT numbers
  • Use BIGINT for very large tables to avoid running out of ids

Common Mistakes

  • Assuming AUTO_INCREMENT produces continuous sequences without gaps
  • Manually inserting ids without understanding conflicts
  • Setting AUTO_INCREMENT to a value lower than an existing max id

Next Step

Continue with SQL Dates to learn how to store and work with date and time values.

SQL Dates →