Skip to Content

How to Define an Auto Increment Primary Key in MySQL

In this tutorial, you'll learn how auto-increment works with primary keys and how to utilize them when working with MySQL database tables. Let's dive into the details of each, then we'll get into some code.

Advertising Disclosure: I am compensated for purchases made through affiliate links. Click here for details.

What is Auto-Increment in MySQL?

Auto-increment provides a unique identifier for a newly inserted record in a MySQL database table using the AUTO_INCREMENT keyword. This is a primary key field whose value is automatically generated each time a new record is created, removing the requirement of executing multiple queries to determine the latest identifier and running a calculation for the new identifier.

An auto-increment field is always an integer type, cannot have a DEFAULT value assigned to it, and must be NOT NULL.

What is a Primary Key in MySQL?

The PRIMARY KEY constraint assigned to a table field is what gives the identifier its uniqueness, and is also a requirement for auto-increment fields.

Each table can only have a single primary key and, just like auto-increment fields, primary keys cannot have DEFAULT values and cannot contain NULL values.

Creating a Table

We'll start by creating a new member table containing an id field with the AUTO_INCREMENT keyword and PRIMARY KEY constraint, along with some data about each member:

first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,

And insert some data into the table:


Since the id column's value is auto-generated, we don't need to set a value manually for it. MySQL knows that this is the primary key field and will auto-generate a new, unique ID for each record created.

By default, the first record created will start with a value of 1 for the id column, followed by 2, 3, and so on, climbing sequentially until the maximum value has been reached for the column's set data type.

If you would like this value to start at an integer other than 1, you could do so by altering the table rule:


This command tells MySQL that you want the id column to start at 100 and count upward for each new record created going forward.

Defining the column name when altering the table is not required in this case since the AUTO_INCREMENT column has already been defined as the id column in our table.

Getting the Latest ID

If you need to get the last inserted identifier in MySQL, you can do this with the LAST_INSERT_ID() function:


Removing a Record with AUTO_INCREMENT

If you remove a record with an auto-generated column identifier, that ID will not be used again. MySQL will continue to auto-generate AUTO_INCREMENT column identifiers sequentially. This is a great measure to have in place in cases where you share these identifiers across multiple tables.


Here, you learned how auto-increment works with primary keys in a MySQL database. It's good practice to use this method when designing your tables to ensure each record has a unique identifier that gets generated automatically.

Posted by: Josh Rowe
Created: May 17, 2022


There are no comments yet. Start the conversation!

Add A Comment

Comment Etiquette: Wrap code in a <code> and </code>. Please keep comments on-topic, do not post spam, keep the conversation constructive, and be nice to each other.