MariaDB AUTO_INCREMENT + Examples

In this MariaDB tutorial, we are going to learn about MariaDB AUTO_INCREMENT. Additionally, we will cover the following topics.

• MariaDB AUTO_INCREMENT
• MariaDB AUTO_INCREMENT_OFFSET
• MariaDB AUTO_INCREMENT Reset
• MariaDB AUTO_INCREMENT By 2 and By 3
• MariaDB AUTO_INCREMENT_INCREMENT
• MariaDB AUTO_INCREMENT Not Primary Key
• MariaDB AUTO_INCREMENT Max Value
• MariaDB AUTO_INCREMENT 0
• MariaDB auto_increment uuid
• MariaDB auto_increment primary key

MariaDB AUTO_INCREMENT

In MariaDB, the AUTO_INCREMENT attribute creates a unique number for each row in a table.

There are the following points that we need to remember while creating a table with the AUTO_INCREMENT column.

• Each table can contain only one AUTO_INCREMENT column.
• Each column in the table must have the constaint of NOT NULL and NULL. If we forget to mention this parameter, then database considers NULL by default. Whenever a first record is inserted into a table, the first record value will start from 1.
• The value which is generated automatically can’t be less than 0 in the case of AUTO_INCREMENT in MariaDB.
• The column which is defined as AUTO_INCREMENT, the same column should be the PRIMARY KEY column.

The syntax to use  AUTO_INCREMENT  in MariaDB is as follows.

CREATE TABLE tablename
( 
  column1 datatype NOT NULL AUTO_INCREMENT,
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Let’s create a table as an Employee and this table contains the information of the employee who works in the restaurant.

CREATE TABLE Employee(
 emp_id INT NOT NULL AUTO_INCREMENT,
 emp_name VARCHAR(255) NOT NULL,
 emp_dept VARCHAR(255) NOT NULL,
 emp_address VARCHAR(255) NOT NULL,
 PRIMARY KEY(emp_id));

• emp_id INT : It is column to store the id of an employee. The column emp_id has defined as AUTO_INCREMENT so that whenever a new record is inserted into a table, column emp_id value will increase automatically. Additionally, all the column has the attribute of NOT NULL which means the column can’t contain the null values.
• emp_name VARCHAR(255): It is the column to store the name of an employee.
• emp_dept VARCHAR(255): It is the column to store the department of an employee in restaurant.
• emp_name VARCHAR(255): It is the column to sotre the address of an employee.

At last, again emp_id has defined as primary key column using the keyword PRIMARY KEY (emp_id),

Now, add some records to the table. To do this, we need to type the following command.

INSERT INTO Employee(emp_name,emp_dept,emp_address) VALUES 
 ('Jemmy','Kitchen ','United States'),
 ('Jhon','Managerial','Canada'),
 ('Adam','Delivery','United Kindom'),
 ('Mark','Kitchen','New York');

SELECT * FROM Employee;

We are inserting the five records in a table Employee. These records contain three information about the employee’s name, department, and address like 'Jemmy', 'Kitchen ', 'United States', 'Jhon','Managerial','Canada', 'Adam','Delivery','United Kindom' and 'Mark','Kitchen','New York'.

As we can see from the output, we didn’t assign any value to emp_id while inserting the records but it has values in a sequence.

This is how we use the MariaDB AUTO_INCREMENT.

Also, check: MariaDB Primary Key With Examples

MariaDB AUTO_INCREMENT_OFFSET

The AUTO_INCREMENT_OFFSET is used to set the start of AUTO_INCREMENT in MariaDB, as we know by default AUTO_INCREMENT start from one. So here we will set the AUTO_INCREMENT to start from different values.

Let’s set the AUTO_INCREMENT value to 4 using the AUTO_INCREMENT_OFFSET. the AUTO_INCREMENT_OFFSET value can be set in two ways:

The first one is for global which means the AUTO_INCREMENT_OFFSET value set for global will work for every session.

The second is a session that will work till the current session, as the session end, the AUTO_INCREMENT_OFFSET value gets reset automatically.

Here we will use it globally.

SET GLOBAL AUTO_INCREMENT_OFFSET = 4;

Here we have used the SET command to set the AUTO_INCREMENT_OFFSET value to 4 and this value is set for GLOBAL use.

Now see the set value of AUTO_INCREMENT_OFFSET using the below query.

SHOW VARIABLES LIKE '%auto_increment_%';

The above code will show the value of all the attributes related to AUTO_INCREMENT.

As from the above output, we have set the AUTO_INCREMENT_OFFSET value to 4. After setting the value, restart the MariaDB server to take effect.

But we can see, there is another variable AUTO_INCREMENT_INCRMENT whose value is set 1.

Note: The AUTO_INCREMENT_OFFSET value should be less than or equal to AUTO_INCREMENT_INCREMENT in MariaDB. if the value is not smaller then, the AUTO_INCREMENT_OFFSET value will be ignored.

So also set the value of AUTO_INCREMENT_INCREMENT to greater than or equal to 4.

SET GLOBAL AUTO_INCREMENT_INCREMENT = 4;

We have set the AUTO_INCREMENT_INCREMENT value to 4, now restart the MariaDB server.

Here we will use the same table that we have used in the above section.

CREATE TABLE Employee(
 emp_id INT NOT NULL AUTO_INCREMENT,
 emp_name VARCHAR(255) NOT NULL,
 emp_dept VARCHAR(255) NOT NULL,
 emp_address VARCHAR(255) NOT NULL,
 PRIMARY KEY(emp_id));

We have used the same table Employee with little changes to AUTO_INCREMENT.

Insert the following records.

INSERT INTO Employee(emp_name,emp_dept,emp_address) VALUES 
 ('Jemmy','Kitchen ','United States'),
 ('Jhon','Managerial','Canada'),
 ('Adam','Delivery','United Kindom'),
 ('Mark','Kitchen','New York');
 
SELECT * FROM Employee;

As we can see from the above output, emp_id the value starts from 4 due to setting the AUTO_INCREMENT_OFFSET value to 4.

Read: MariaDB Timestamp

MariaDB AUTO_INCREMENT Reset

In MariaDB, the AUTO_INCREMENT value can be reset using the ALTER TABLE statement.

Let’s see with an example using the same table Employee.

CREATE TABLE Employee(
 emp_id INT NOT NULL AUTO_INCREMENT ,
 emp_name VARCHAR(255) NOT NULL,
 emp_dept VARCHAR(255) NOT NULL,
 emp_address VARCHAR(255) NOT NULL,
 PRIMARY KEY(emp_id));

Creating the table Employee as we have created in the above section.

Insert the following records.

INSERT INTO Employee(emp_name,emp_dept,emp_address) VALUES 
 ('Jemmy','Kitchen ','United States'),
 ('Jhon','Managerial','Canada'),
 ('Adam','Delivery','United Kindom'),

View the table data using the below query.

SELECT * FROM Employee;

As from the above output, we have inserted the three records in a table Employee, Now delete the last record which is emp_id equal to 3.

DELETE FROM Employee
WHERE emp_id = 3;

SELECT * FROM Employee;

In the above code, delete the record whose emp_id is 3 which is specified in the WHERE clause using the DELETE command.

Now the Employee table looks like this

After deleting the record, we have only two records related to employees. If we insert the new record then AUTO_INCREMENT is going to assign emp_id as 4.

Because the current value of AUTO_INCREMENT is 3 but here we have two records with emp_id of 1 and 2. We already know that it will assign 4 to emp_id as we insert the third record.

The solution is to reset the AUTO_INCREMENT value using the ALTER TABLE statement.

ALTER TABLE Employee AUTO_INCREMENT = 3;

The above command will reset the AUTO_INCREMENT value to 3 of table Employee.

Now, the AUTO_INCREMENT will assign value 3 to the third record that we are going to insert using the below code.

INSERT INTO Employee(emp_name,emp_dept,emp_address) VALUES ('Mark','Kitchen','New York');

SELECT * FROM Employee;

As from the output, the third record got the emp_id as 3.

Read: How to Change Column in MariaDB

MariaDB AUTO_INCREMENT By 2 and By 3

In MariaDB, the AUTO_INCREMENT can increment the value by 2 and 3 at each step using the AUTO_INCREMENT_INCREMENT that decides the interval between the ids. we have already learned about how to set the AUTO_INCREMENT_INCREMENT in the above sections.

Let’s set the ATUO_INCREMENT_INCREMENT to 2 using the below code.

SET GLOBAL AUTO_INCREMENT_INCREMENT = 2;

After running the above code, restart the MariaDB server to take effect.

Create a new table as Countries using the below query.

CREATE TABLE Countries(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, country_name VARCHAR(30));

Here in the code, we have defined the table as Countries with columns:

• id INT: It is a column of type integer to store the id of each country and it has attributes of AUTO_INCRMENT, NOT NULL and PRIMARY KEY.
• country_name VARCHAR(30): It is a column of type character or VARCHAR(30) to store the name of the country.

Insert the following records by typing the below query.

INSERT INTO Countries(country_name)VALUES('United Kindom'),('New York'),('Canada'),('United Kindom');

We are inserting the five records as 'United Kindom', 'New York', 'Canada' and 'United Kindom'.

In the above code at line number 40, we haven’t assigned any value to a column id but as we can see in the output AUTO_INCRMENT assigned the unique number to each record automatically.

There is a difference of two between each unique number because we have set the AUTO_INCRMENT_INCREMENT value to 2.

The same steps can be applied to AUTO_INCREMENT by 3 using the below codes.

SET GLOBAL AUTO_INCREMENT_INCREMENT = 3;

Again create the same table that we created above.

• id INT: It is a column of type integer to store the id of each country and it has attributes of AUTO_INCRMENT, NOT NULL and PRIMARY KEY.

• country_name VARCHAR(30): It is a column of type character or VARCHAR(30) to store the name of the country.

CREATE TABLE Countries(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, country_name VARCHAR(30));

Insert the same records that contain the name of countries like United Kindom, New York, Canada, and United Kindom.

INSERT INTO Countries(country_name)VALUES('United Kindom'),('New York'),('Canada'),('United Kindom');

Read: MariaDB Update Statement

MariaDB AUTO_INCREMENT_INCREMENT

In MariaDB, we have already learned about the AUTO_INCREMENT_INCREMENT to increase the AUTO_INCREMENT column by given factors like 2, 3, etc.

If the AUTO_INCREMENT_INCREMENT value is set to 4 then the first record will have the id as 1 and the second record as 5 and the fourth as 9.

Set the AUTO_INCREMENT_INCREMENT value to 4 using the below code.

SET GLOBAL AUTO_INCREMENT_INCREMENT = 4;

In the above code, we have set the AUTO_INCREMENT value to 4 which will be applied globally. After running the above code restart the MariaDB server to take effect of changes.

Verify the changes by typing the below code.

SHOW VARIABLES LIKE '%auto_increment_%';

From the output, we have changed the AUTO_INCREMENT_INCREMENT value to 4 successfully.

Now create a new table as Person with AUTO_INCREMENT column.

CREATE TABLE Person(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30),address VARCHAR(30));

Here In the above code, we have defined the new table as Person with columns:

• id INT: It is a column of type integer to store the id of each country and it has attributes of AUTO_INCRMENT, NOT NULL and PRIMARY KEY.
• name VARCHAR(30): It is a column of type character to store the name of a person till 30 character.
• address VARCHAR(30): It is a column of type character to store the address of a person.

Insert the following records in a Person table.

INSERT INTO Person(NAME,address)VALUES('Leo Harper','USA'),('Jenson Moss','United Kindom'),('Thomas Parry','New York');
SELECT * FROM Person;

The output shows that the column id has values 1, 5, and 9 in sequence. if we will calculate the difference between any two ids of records, then we get the value 4 as a result because the AUTO_INCREMENT_INCREMENT value is set to 4.

Read: Create View in MariaDB

MariaDB AUTO_INCREMENT Not Primary Key

In MariaDB, we can create the table with column AUTO_INCREMENT and without defining the same column as PRIMARY KEY.

The AUTO_INCREMENT column doesn’t need to be the PRIMARY KEY column. So we make another column as PRIMARY KEY.

Why do we make another column as PRIMARY KEY?, because the table with AUTO_INCREMENT must contain the PRIMARY KEY.

Here we will use the same table of Countries that we created in the above section.

CREATE TABLE Countries (
  `id` INT(11) NOT NULL,
  `country_name` VARCHAR(45) DEFAULT NULL,
  `contryInc` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `contryInc` (`contryInc`)
)

In the above code, we have created the new table Countries using the CREATE STATEMENT with columns:

• id INT(11): The column to store the id of the country. It is also defined as NOT NULL and PRIMARY KEY column.
• country_name VARCHAR(45): It is a column of type characters to store the name of the country.
• contry_Inc INT(11): It is a column of type integer to store the index generated by the AUTO_INCREMENT command. It has atteribute of NOT NULL and AUTO_INCREMENT as we can see in the above code.

Here column contryInc act as an index for the table which is defined as a KEY and AUTO_INCREMENT. But this time we haven’t defined the AUTO_INCREMENT column as PRIMARY KEY instead we have used another column id as PRIMARY KEY column.

Insert the same records and view the table using the below query.

INSERT INTO Countries(id,country_name)VALUES(1,'United Kindom'),(2,'New York'),(3,'Canada'),(4,'United Kindom');
SELECT * FROM Countries;

From the above output, look at the column contryInc which is an index of this table containing the value generated by AUTO_INCREMENT.

This is how we create a column with AUTO_INCREMENT and without PRIMARY KEY.

Read: Replace Function in MariaDB

MariaDB AUTO_INCREMENT Max Value

The max value of the AUTO_INCREMENT is 2.1 billion in MariaDB.

MariaDB AUTO_INCREMENT 0

The AUTO_INCREMENT value can’t be set to 0 for a new record that will be inserted in a table of MariaDB. The value can be set by using the UPDATE TALBE and AUTO_INCREMENT.

Remember that the AUTO_INCREMENT_OFFSET value can’t be set to zero, this AUTO_INCREMENT_OFFSET value decides the starting value of AUTO_INCREMENT. So here we will do that manually.

Let’s create a new table as a Person.

CREATE TABLE Person(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30),address VARCHAR(30));

In the above code, we have defined the new table as Person with columns:

• id INT: It is a column of type integer to store the id of each country and it has attributes of AUTO_INCRMENT, NOT NULL and PRIMARY KEY.
• name VARCHAR(30): It is a column of type characters to store the name of person.
• address VARCHAR(30): It is a column of type characters to store the address of a person.

After creating the table, insert only one record in a table using the below code.

INSERT INTO Countries(country_name)VALUES('United Kindom');
SELECT * FROM countries;

As we can see from the output, here id for that column is one but we want it as zero.

To achieve the value zero for the id column, we can’t make any changes to the AUTO_INCREMENT_OFFSET variable to zero. So that whenever a new record will be inserted, then this record will have the id column as zero.

Note: The current AUTO_INCREMENT value is one as we inserted a record in a table.

First, update the id column value to zero using the below code.

UPDATE Countries
SET id = 0
WHERE country_name = 'United Kindom';

SELECT * FROM countries;

We are updating the column id value to zero where country_name is equal to United Kindom.

We have updated the id value to zero, but AUTO_INCREMENT’s current value is one, if we insert the next record AUTO_INCREMENT will assign a unique number as 2, Let’s alter the AUTO_INCREMENT value to 1 using the below code.

ALTER TABLE Countries AUTO_INCREMENT = 1;

Using the above code, we have reset the AUTO_INCREMENT value to one so that whenever a new record will be inserted, it will assign the value as one to that record.

Now again insert the following records.

INSERT INTO Countries(country_name)VALUES('New York'),('Canada'),('United Kindom');

SELECT * FROM countries;

From the output, we can see that the id column value starts from zero.

Read: MariaDB regexp + Examples

MariaDB Auto_Increment uuid

In MariaDB, The UUID is another data type is utilized to store data with a 128-bit UUID (Universally Unique Identifier).

The UUID data type can be cast to strong literals like hexadecimal characters and CHAR/ VARCHAR/ TEXT. UUID can also be cast from hexadecimal literals, binary literals, and BINARY/VARBINARY/BLOB types.

This data type will not accept a short UUID generated with the UUID-SHORT function but it will accept a value without the character generated by the SYS-GUID function. Hyphens can be partially omitted as well or included after any two digits.

The syntax of the UUID is given below:

SYNTAX:

UUID

The sample example of the UUID is given below:

EXAMPLE:

CREATE TABLE STUDENT_ACTIVITIES(
STUDENT_ID VARCHAR(50),
STUDENT_NAME VARCHAR(30) NOT NULL,
ACTIVITY_1 VARCHAR(40) NOT NULL,
COST_1 BIGINT,
ACTIVITY_2 VARCHAR(40),
COST_2 INT);

INSERT INTO STUDENT_ACTIVITIES VALUES
(UUID(),'John Smith','Tennis',36,'Swimming',17),
(UUID(),'Jane Bloggs','Squash',40,'Swimming',17),
(UUID(),'John Smith','Tennis',36,'',56),
(UUID(),'Mark Anthony','Swimming',15,'Golf',47);

SELECT * FROM STUDENT_ACTIVITIES;

As we in the above query, we have created a UUID (universal unique identifier) for the student_id column by using the INSERT statement. During the insertion time, we have used the UUID() function to created hexadecimal or binary literal data type for the student_id column.

MariaDB Auto_Increment Primary Key

In this section, we will learn how to use auto_increment constraint with primary key KEY with the syntax and the examples.

In MariaDB, a primary key is a single field or combination of fields that uniquely defines the record in the table. None of the fields in the PRIMARY KEY column contains the NULL value. A table can have one primary key only.

We can create a table with the help of the PRIMARY KEY including the auto_increment constraint in the table by using the CREATE TABLE statement.

The syntax to create a PRIMARY KEY by using the CREATE TABLE constraint is given below:

CREATE TABLE TABLE_NAME(
COLUMN_NAME1 DATATYPE,
COLUMN_NAME2 DATATYPE,
.....,

CONSTRAINT [COLUMN_NAME]
PRIMARY KEY [USING BTREE | HASH] (COLUMN1,COLUMN2,.... COLUMN N)
) 

The syntax explanation:

• table_name: the name of the table that we want to create.
• column1,column2: the columns that we want to create in table. See in MariaDB CREATE TABLE statement for detailed CREATE TABLE syntax as this proper way to demonstrate how to create the primary key.
• constraint_name: the name of the primary key.
• column1,column2,… column n: the columns that makes the primary key.

The sample example to create a table with PRIMARY KEY including AUTO_INCREMENT constraint with the help of the CREATE TABLE constraint is given below:

CREATE TABLE TEACHER_ACTIVITIES(
TEACHER_ID INT AUTO_INCREMENT PRIMARY KEY,
TEACHER_NAME VARCHAR(50) NOT NULL,
SUBJECTS_1 VARCHAR(20),
COST_1 INT,
SUBJECT_2 VARCHAR(30),
COST_2 INT);

INSERT INTO TEACHER_ACTIVITIES
(TEACHER_NAME,SUBJECTS_1,COST_1,SUBJECT_2,COST_2)
VALUES
('Ms. Jasson Smith','Physics',34,'Maths',20),
('Mrs. Larryson levie','Chemistry', 40,'Enviromental Studies',10),
('Mr. Robinson Oberia','Physical Studies',30,'Chemistry',40),
('Mr. Robin Anthony','English',60,'Management Studies',100),
('Mrs. S Nair','Stock Market',120,'Information Studies',40);

SELECT * FROM TEACHER_ACTIVITIES;

• As we see in the above query, we have created a TEACHER_ACTIVITIES table by using the CREATE TABLE statement.
• In the TEACHER_ACTIVITES table, we have created a TEACHER_ID column by using auto_increment constraint and PRIMARY KEY key.
• And with these constraints, it automatically generated 5 rows starting from the teacher_id column as 1 default.

You may also like to read the following MariaDB tutorials.

• MariaDB Delete Row
• MariaDB Full Outer Join
• MariaDB Cast with Examples
• MariaDB LIMIT + Examples
• MariaDB on Duplicate Key Update

In this MariaDB tutorial, we have learned about MariaDB AUTO_INCREMENT. Additionally, we have covered the following topics.

• MariaDB AUTO_INCREMENT
• MariaDB AUTO_INCREMENT_OFFSET
• MariaDB AUTO_INCREMENT Reset
• MariaDB AUTO_INCREMENT By 2 and By 3
• MariaDB AUTO_INCREMENT_INCREMENT
• MariaDB AUTO_INCREMENT Not Primary Key
• MariaDB AUTO_INCREMENT Max Value
• MariaDB AUTO_INCREMENT 0
• MariaDB auto_increment uuid
• MariaDB auto_increment primary key