MariaDB Create Sequence + Examples

In this MariaDB tutorial, we will learn about the “MariaDB Create Sequence” which generates a number automatically. Additionally, we will also cover the following topics.

• MariaDB create sequence
• MariaDB drop sequence
• MariaDB alter sequence
• MariaDB create sequence if not exists

MariaDB create sequence

In MariaDB, the new sequence is created using the keyword CREATE SEQUENCE that generates a new number when we call it NEXT VALUE FOR sequence_name.

• It is alternate to AUTO_INCREMENT, but faster than AUTO_INCREMENT because it caches the values.
• It provides us more control that how numbers should be generated.

The full syntax is given:

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] 
[ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE] 
[table_options]

The argument that can be used with CREATE SEQUENCE is given below:

• INCREMENT: It is used to increment the value by a certain number like 2,3,4 etc.
• MINVALUE: It is used to set the minimum value for a sequence.
• MAXVALUE: It is used to set the maximum value for a sequence.
• START WITH: It is used to set the starting value of the sequence or which value it will generate when we call it the first time.
• CACHE: It is used to define the number of values that will be cached.

Let’s take an example

CREATE SEQUENCE United_States START WITH 1 INCREMENT BY 2;

• CREATE SEQUENCE: It is the command to create a sequence. The United_States is the name of the sequence that we are creating.

• START WITH: It is used to specify the starting value of the sequence that will be generated as we specified 1.

• INCREMENT BY: It is used to set the value of increment means how much value should be incremented each time sequence is called. So here we have specified the value of INCREMENT BY 2.

To call the above sequence in MariaDB, use the below command.

SELECT NEXT VALUE FOR sequence_name;

Also, to get the next value of the sequence, we are using the above command

• NEXT VALUE FOR: This command gives the next value of the sequence.
• sequence_name: It is the name of sequence that we are calling or that we created before calling it.

Now, let’s execute an example in MariaDB and for this, consider the following query.

CREATE SEQUENCE United_States START WITH 1 INCREMENT BY 2;

SELECT NEXT VALUE FOR United_States;

Alternate to the above command, we could have also used the below command to get the next value of the sequence.

SELECT NEXTVAL(name_of_sequence);

Where NEXTVAL command is used instead of NEXT VALUE FOR.

SELECT NEXTVAL(United_States);

As we can see from the output of both commands NEXT VALUE FOR and NEXTVAL, we achieved the same result and that is 1. It generates the value 1 because we have set the starting value of the sequence as 1.

If we run the same command again, then we will get the new value as 3 because the sequence is set to increment the value by 2 each time it is called. Let’s see by running the below command.

SELECT NEXTVAL(United_States);

To show the previous value of the sequence in MariaDB, use the below syntax.

SELECT PREVIOUS VALUE FOR name_of_sequence;
                            OR
SELECT LASTVAL(name_of_sequence);

Where,

• PREVIOUS VALUE FOR: It is a command to get the previous value of the sequence.

• name_of_seuquence: This is the name of the sequence.
• LASTVAL: It is alternate command for PREVIOUS VALUE FOR.

SELECT PREVIOUS VALUE FOR United_States;

To view the sequence and its properties in MariaDB, use the below syntax.

SHOW CREATE SEQUENCE name_of_sequence;

Where SHOW CREATE SEQUENCE is the command to view the sequence and provide the name of sequence as name_of_sequence.

SHOW CREATE SEQUENCE United_States;

Until now, we have called the sequence using the SELECT statement. Let’s use the sequence in a table to generate the value for a column.

Create a new sequence as Country_Seq.

CREATE SEQUENCE Country_Seq START WITH 1 INCREMENTED BY 1;

Create a new table as Countries.

CREATE TABLE Countries(id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR Country_Seq), country_name VARCHAR(30));

Here in the above code, we have used the command CREATE TABLE to create a new table as Countries with columns:

• id INT: It is the column of type integer to store the value generated by a sequence and also the column is defined as PRIMARY KEY. The default value of the column will be determined by the command NEXT VALUE FOR Country_Seq.
• country_name VARCHAR(30): It store the name of country as character till 30 character long.

Insert the following record in a column country_name.

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

SELECT * FROM Countries;

Inserting the three records like 'Canada', 'United Kindom' and 'USA' into a table Countries.

As we can see in the above output, we haven’t assigned any value to the column id but it has value for each record in a table. This is due to the sequence that we have defined for this column as DEFAULT (NEXT VALUE FOR Country_Seq).

Also, check: MariaDB Primary Key With Examples

MariaDB drop sequence

The DROP SEQUENCE is used to drop the sequence in MariaDB, for dropping the sequence each sequence should have the drop privilege.

The syntax is given below.

DROP SEQUENCE (IF EXISTS) name_of_sequence;

where,

• DROP SEQUENCE: It is the command to drop the sequence.
• IF EXISTS: It is used to prevent the error that occurs when the sequence is dropped which doesn’t exist.
• name_of_sequence: The name of the sequence that we want to drop.

Let’s take an example by creating a new sequence in MariaDB.

CREATE SEQUENCE Person_id START WITH 1 INCREMENT BY 1;

Here, we have created the sequence as Person_id and the sequence starts with 1, and each time Person_id will be incremented by 1 when the sequence is called.

View the created sequence using the below code.

SHOW CREATE SEQUENCE Person_id;

From the output, we can see the created sequence as Person_id, Let’s drop this sequence using the below code.

DROP SEQUENCE IF EXISTS Person_id;

After running the above code, the sequence gets dropped. To verify it, use the same code that we use for viewing the sequence, it will show an error that the sequence doesn’t exist.

Read: MariaDB Delete Row

MariaDB alter sequence

In MariaDB, The ALTER SEQUENCE command is used to modify the parameters of the existing sequence. By altering the sequence we can add or remove the parameters of the sequence.

The syntax is given below.

ALTER SEQUENCE [IF EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ]
[ RESTART [[WITH | =] restart]

Where arguments are:

• ALTER SEQUENCE: It is a command to change the sequence parameters.
• INCREMENT: It is used to increment the value by a certain number like 2,3,4 etc.
• MINVALUE: It is used to set the minimum value for a sequence.
• MAXVALUE: It is used to set the maximum value for a sequence.
• START WITH: It is used to set the starting value of the sequence or which value it will generate when we call it the first time.
• CACHE: It is used to define the number of values that will be cached.

Create a new sequence using the below code.

CREATE SEQUENCE Emp_Seq START WITH 1 INCREMENT BY 1;

SELECT NEXT VALUE FOR Emp_Seq;

Here, we have created the sequence as Emp_Seq, which starts with 1 and, is incremented by 1. Then called it using the NEXT VALUE FOR Emp_Seq.

Now alter the INCREMENT parameters of this sequence from 1 to 2 which means Emp_Seq will increment by 2 each time.

ALTER SEQUENCE Emp_Seq INCREMENT 2;

where,

• ALTER SEQUENCE: It is the command to modify the existing sequence.
• Emp_Seq: This is the name of the sequence that we are modifying.
• INCREMENT: It is parameter or argument that is modified, here we have specified the value as 2.

Check the changes by typing the below code.

SHOW CREATE SEQUENCE Emp_Seq;

Here is the output of the above example.

Read: MariaDB LIMIT + Examples

MariaDB create sequence if not exists

As we already know how to create a sequence in MariaDB directly, here we will use one of the options IF NOT EXISTS while creating the sequence. This option checks for an already existing sequence of the same name that we are going to create.

if the same name of a sequence exists, then it doesn’t create the new one.

Let’s check with an example.

Create a new sequence as Customer_id by typing the code given below.

CREATE SEQUENCE Customer_id START WITH 1 INCREMENT BY 1;

• CREATE SEQUENCE: It is the command to create a sequence. The Customer_id is the name of the sequence that we are creating.

• START WITH: It is used to specify the starting value of the sequence that will be generated as we specified 1.

• INCREMENT BY: It is used to set the value of increment means how much value should be incremented each time sequence is called. So here we have specified the value of INCREMENT BY 1.

View the created sequence using the below code.

SHOW CREATE SEQUENCE Customer_id;

As we can see from the output, we have successfully created the sequence as Customer_id.

Now create the same sequence with parameters IF NOT EXISTS by typing the below code.

CREATE SEQUENCE IF NOT EXISTS Customer_id START WITH 1 INCREMENT BY 1;

As soon as, we run the above code, it shows a warning like ‘Customer_id sequence already exists.

As the output shows the warning for the sequence that we are creating, the warning is due to the sequence already existing.

So it is a good idea to check the sequence before creating it using the option IF NOT EXISTS parameter.

You may also like to read the following MariaDB tutorials.

• MariaDB LIKE Operator
• MariaDB Create Procedure
• MariaDB Update Statement
• MariaDB regexp + Examples
• MariaDB DateTime Tutorial
• Replace Function in MariaDB
• MariaDB ISNULL + Examples

So, in this tutorial, we have learned about the MariaDB Create Sequence and also covered the following topics.

• MariaDB create sequence
• MariaDB drop sequence
• MariaDB alter sequence
• MariaDB create sequence if not exists