MariaDB Set Auto Increment Value

In this MariaDB tutorial, we will know how to set the auto-increment value in MariaDB.

Sometimes database developer needs to design a database that should keep each record in a unique way or the record can be found easily, for that auto-increment is used. The auto-increment assigns a unique identity to each record or information in the database automatically as records are inserted.

So, we are going to focus on how to set the auto-increment value for a column in MariaDB by following the below topics.

• MariaDB Set Auto Increment Value
• MariaDB How to set Auto Increment Value
• MariaDB Set Auto Increment Start Value

MariaDB Set Auto Increment Value

To create a distinct identity for new rows, utilize the AUTO INCREMENT property. The auto-increment field’s value will be automatically increased when a new record is inserted into the table (or when an AUTO INCREMENT attribute is added with the ALTER TABLE statement).

• By default, AUTO INCREMENT columns begin at 1. The value that was automatically generated can never be less than 0.
• There can only be one AUTO INCREMENT column per table.
• It should be defined as a key, not just as the UNIQUE key or the PRIMARY KEY.

Let’s understand with an example by following the below steps:

First, create a table named users with an auto-increment column using the below query.

CREATE TABLE users (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     country VARCHAR(200),
     PRIMARY KEY (id)
 );

Insert the following record into the above-created table.

INSERT INTO users (name,country) VALUES
    ('James','USA'),
    ('Jhon','Australia'),
    ('Jhony','USA');

Now view the table users for the auto-increment values.

SELECT * FROM users;

In the above picture, look at line number 2 where the id column is defined as the AUTO_INCREMENT column. As we can see when inserted the data into the table users, it automatically updated the column id values like 1,2, and 3. This value we haven’t provided while inserting the data.

But the auto-increment value starts from 1 and increases the value each time when data is inserted. Let’s say we want to start the next value of auto-increment from 6. For that, we will set the auto-increment value to a different value using the ALTER TABLE statement.

The syntax is given below.

ALTER TABLE tbl_name AUTO_INCREMENT = new_value;

Let’s see with an example and change the value of the AUTO INCREMENT.

ALTER TABLE users AUTO_INCREMENT = 6;

Insert new data into a table user.

INSERT INTO users (name,country) VALUES
    ('Robin','USA');
	 
SELECT * FROM users;

As soon as, we inserted the data “INSERT INTO users (name,country) VALUES (‘Robin’,’USA’);” into table users. The id of the new row or data became 6 as we can see in the above output because of the AUTO INCREMENT value. This is how to set the auto-increment value in MariaDB.

Read: MariaDB Difference Between Two Dates

MariaDB How to set Auto Increment Value

We have already learned how to set the AUTO INCREMENT value using the ALTER TABLE statement. In this section, we will learn a new way to set the auto-increment value. To modify the next AUTO INCREMENT value entered by the current session, set the insert_id server system variable.

The syntax is given below.

SET insert_id = new_value

Let’s take an example by following the below steps:

Set the value of variable insert_id to 9 using the below command.

SET insert_id = 10;

Insert new data into a table user.

INSERT INTO users (name,country) VALUES
    ('Emma','United Kingdom');
	 
SELECT * FROM users;

From the above output, we can see the newly inserted data with an id value equal to 10, this is because we have set the auto-increment value to 10. This is how to set the auto-increment value using the SET command.

Read: MariaDB Select Where Not Empty

MariaDB Set Auto Increment Start Value

To set the start value of the auto-increment value, we can either use the ALTER TABLE or SET command that we have learned in the above subsections.

Let’s take an example by following the below steps:

Create the same table that we have created in the above subsection using the below command.

CREATE TABLE users (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     country VARCHAR(200),
     PRIMARY KEY (id)
 );

Use the below command to set the starting value of auto-increment to 3.

SET insert_id = 3;

Insert the following record into the above-created table.

INSERT INTO users (name,country) VALUES
    ('James','USA'),
    ('Jhon','Australia'),
    ('Jhony','USA');

Now view the table users for the auto-increment values.

SELECT * FROM users;

From the above output, we can see the starting value of the id column is 3 which we have set the auto-increment value using the “SET insert_id = 3;” command. This is how to set the starting value of the auto-increment value.

Read: MariaDB Date Add Days

We have covered how to set the auto-increment value of the column in MariaDB, and also learned how to set the auto-increment value while inserting the data. Additionally, we have covered the following topics.

• MariaDB Set Auto Increment Value
• MariaDB How to set Auto Increment Value
• MariaDB Set Auto Increment Start Value

You may also like to read the following MariaDB tutorials.

• MariaDB Check If Rows Exists
• Add Data To Table MariaDB
• How to Show Tables in MariaDB
• MariaDB Check Constraint
• MariaDB Insert Multiple Rows
• MariaDB Insert Into Select