MariaDB: Reset the Next Value in AUTO_INCREMENT column

This MariaDB tutorial explains how to reset sequences using the AUTO_INCREMENT attribute in MariaDB with syntax and examples.

Description

You can reset the next value assigned by the AUTO_INCREMENT at any time using the ALTER TABLE statement in MariaDB.

Syntax

In MariaDB, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is:

ALTER TABLE table_name AUTO_INCREMENT = value;

table_name

The name of the table whose AUTO_INCREMENT column you wish to reset.

value

The next value that will be used in the AUTO_INCREMENT column.

Example

Let's look at an example of how to reset the next value assigned to an AUTO_INCREMENT column in a table in MariaDB.

For example, if we had a websites table that was defined as follows:

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id)
);

We could reset the next value in the sequence for the website_id field (which is the AUTO_INCREMENT field in the websites table) with the following ALTER TABLE statement:

ALTER TABLE websites AUTO_INCREMENT = 1;

This example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1 for the website_id column in the websites table. Now when a new record is inserted into the websites table, the website_id column will be assigned the value of 1 in the newly created record.