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.

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:

CREATE TABLE member (
	id BIGINT NOT NULL AUTO_INCREMENT,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
);

And insert some data into the table:

INSERT INTO member
		(first_name,
		 last_name)
	VALUES
		('Josh',
		 'Rowe');

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:

ALTER TABLE member AUTO_INCREMENT = 100;

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:

SELECT LAST_INSERT_ID();

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.

Conclusion

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.