MariaDB Set Auto Increment Value

The execution of the MariaDB Set Auto Increment Value will be covered in this MariaDB tutorial. To assist you in better understanding the topic, we shall explore and conclude several situations in this section. The whole list of topics we’ll cover is given below.

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

MariaDB Set Auto Increment Value

Here we will learn how to use the MariaDB ALTER TABLE statement to set the AUTOINCREMENT value of the column in the table by the query. And which will be explained with the help of an illustrated example.

Using the AUTOINCREMENT feature in MariaDB, you can build a column that has a series of numbers (1, 2, 3, and so forth). When you need to generate a unique integer to serve as a primary key in a database, you utilize the AUTOINCREMENT property.

We can use the ALTER TABLE statement to change or set a new value assigned to the AUTO_INCREMENT. Here is the syntax of the MariaDB ALTER TABLE statement to change the auto increment value of the column by the following query:

SYNTAX:

ALTER TABLE YOUR_TABLE_NAME AUTO_INCREMENT= STARTING_VALUE;

In the syntax explanation:

  • YOUR_TABLE_NAME: The name of the table whose AUTO_INCREMENT value we want to change. In MariaDB, since one table can carry one AUTO_INCREMENT column, it only requires specifying the table_name that contains the sequence. And we don’t need to specify the column_name which carries the AUTO_INCREMENT value.
  • STARTING_VALUE: The following value in the list should be entered in the AUTO_INCREMENT column.

Let’s see a sample example of the MariaDB ALTER TABLE statement to set the auto-increment value of the table by the following query:

EXAMPLE:

ALTER TABLE COMPANIES_EMPLOYEES AUTO_INCREMENT=2;

SELECT * FROM COMPANIES_EMPLOYEES;

As we see in the above query, we have used the ALTER TABLE statement to change the value of the sequence from 2 in the COMPANIES_EMPLOYEES table. If we want to check it, we have used the SELECT statement to retrieve all records from the COMPANIES_EMPLOYEES table.

MariaDB set auto increment value example
Example of MariaDB Set Auto Increment Value

We hope that you have understood the subtopic “MariaDB Set Auto Increment Value” by using the MariaDB ALTER TABLE statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: MariaDB Difference Between Two Dates

MariaDB Set Auto Increment Start Value

Here we will learn and understand how to use the MariaDB ALTER TABLE statement to set the auto-increment start value of the column in the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

ALTER TABLE HARVARD_UNIVERSITY AUTO_INCREMENT=20;

INSERT INTO HARVARD_UNIVERSITY(STUDENT_FIRSTNAME,STUDENT_LASTNAME,EMAIL,GENDER,
STUDENT_ADMITDATE,STUDENT_ENDDATE)
VALUES('James','Bond','bond.james007@bondfilms.com','Male','2021-08-04 12:54:26','2025-09-14 04:06:09');

SELECT * FROM HARVARD_UNIVERSITY;

In the aforementioned query, we have used the ALTER TABLE statement to add the AUTO_INCREMENT value as 20 in the HARVARD_UNIVERSITY table. Then, when we inserted a new record into the HARVARD_UNIVERSITY table, the AUTO_INCREMENT value will be 20 and the next continued value will be 21.

And therefore, if we want to check new Auto_Increment value has been updated or not then we will use the SELECT statement to retrieve all records from the HARVARD_UNIVERSITY table.

MariaDB set auto increment start value example
Example of MariaDB Set Auto Increment Start Value

We hope that you have understood the subtopic “MariaDB Set Auto Increment Start Value” by using the MariaDB ALTER TABLE and INSERT INTO statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: MariaDB Select Where Not Empty

MariaDB How to set Auto Increment Value

Here we will learn and understand the subtopic “MariaDB How to set Auto Increment Value” by using the MariaDB ALTER TABLE statement on the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

ALTER TABLE JOHNS_HOPKINS_HOSPITAL AUTO_INCREMENT=50;

INSERT INTO JOHNS_HOPKINS_HOSPITAL (PATIENT_FIRSTNAME,PATIENT_LASTNAME,PATIENT_EMAIL,GENDER,PATIENT_ADMITDATE,
PATIENT_DISCHARGEDATE)
VALUES('Charlie','Brown','brown.charlie@msn.microsoft.com','Male','2021-04-06 06:00:53','2022-04-06 07:30:25');

SELECT * FROM JOHNS_HOPINS_HOSPITAL
WHERE PATIENT_ID>=38;

In the above query, we have used the ALTER TABLE statement to set the new AUTO_INCREMENT value as 50 in the JOHNS_HOPKINS_HOSPITAL table. This means that new records will be inserted into the JOHNS_HOPKINS_HOSPITAL table from 50 and so on.

Then we inserted a new record by using the INSERT INTO statement into the JOHNS_HOPKINS_HOSPITAL table. If we want to check new record has been inserted into the JOHNS_HOPKINS_HOSPITAL table or not for that we have used the SELECT statement to retrieve all records from the JOHNS_HOPKINS_HOSPITAL table with the WHERE condition.

In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 38 from the JOHNS_HOPKINS_HOSPITAL table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table.

But if the SELECT statement gets executed successfully and retrieve empty record set from the JOHNS_HOPKINS_HOSPITAL table only when the WHERE condition gets a FALSE value.

MariaDB how to set Auto Increment value example
Example of MariaDB How to set Auto Increment Value

We hope that you have understood how to set Auto Increment value in the table by using the MariaDB ALTER TABLE statement by the query. For a better explanation, we have used an example and described it in depth.

Read: MariaDB Date Add Days

Insert Auto Increment Value in MariaDB

Here we will learn and understand how to insert auto increment value in MariaDB by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

ALTER TABLE CUSTOMER_LIST AUTO_INCREMENT=25;

INSERT INTO CUSTOMER_LIST (first_name, LAST_NAME, EMAIL, PRICE)
VALUES('Joe','Beiden','beiden.joe@usa_congress.com',5420),
('Satya','Nadella','nadella.satya@microsoft.com',5412);

SELECT * FROM CUSTOMER_LIST
WHERE ID>=18
ORDER BY first_name ASC,
LAST_NAME DESC;

As we see in the above query, the ALTER TABLE statement is to alter and set new AUTO_INCREMENT value as 25 from the CUSTOMER_LIST table. To check new AUTO_INCREMNT value has been updated or not, first we have used the INSERT INTO statement to insert two new records into the CUSTOMER_LIST table.

After that, once a new record has been inserted then we have used the SELECT statement to retrieve all records from the CUSTOMER_LIST table with the WHERE condition.

In the WHERE condition, the ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 18 and also arrange the records of the FIRST_NAME and LAST_NAME columns in ascending and descending order by using the ORDER BY expression [ ASC | DESC ] in the CUSTOMER_LIST table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CUSTOMER_LIST table otherwise vice-versa.

Insert auto increment value in MariaDB example
Example of Insert Auto Increment Value in MariaDB

We hope that you have understood the subtopic “Insert Auto Increment Value in MariaDB” by using the MariaDB ALTER TABLE and INSERT INTO statement on the table by the query. For a better illustration, we have used a demonstrated example and explained it in depth.

You may also like to read the following MariaDB tutorials.

By the end of this MariaDB tutorial to might understand the use of MariaDB Set Auto Increment Value reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

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