Do you think IIT Guwahati certified course can help you in your career?
Introduction
This article is about the vital uses of sequences in MySQL and Oracle. We would be looking at the requirement of sequences, their basic syntax, and some examples of sequences that will enable you to understand them better. MySQL also offers one keyword, i.e., AUTO_INCREMENT, which will make sequences easier.
Let us begin with the understanding of sequences in MySQL.
What are sequences in MySQL?
We all have encountered “Serial number/Serial Id” in a table many times. We can understand Sequences in MySQL in the same manner.
These are a set of integers generated to produce unique values when demanded. It is a user-defined schema-bound object which we develop as there are many applications in MySQL that require unique values in each row of a table. With its help, we can directly obtain the desired values from a huge table.
Sequences are always either in increasing or decreasing order with a specific interval.
Importance of sequences
The architecture of the computer says that the CPU generates the instructions in accordance with the secondary memory, but the secondary memory is very slow and thus cannot compete with the instructions given by the CPU. Therefore the main memory (RAM) and cache store the desired information, and we do it with the help of sequences.
It also guarantees to return unique values in each table row to be used as a primary key.
These were the importance of sequences.
Now that we have learned its importance, we will move forward and see how to write it and the correct syntax to use it.
Syntax
Let us look at the basic syntax of using sequences in Oracle.
CREATE SEQUENCE sequence_name
AS type_as_integer
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CACHE/NOCACHE
CYCLE/NOCYCLE ;
Sequence name
At first, we must name the sequence corresponding to the CREATE SEQUENCE statement.
Type
As discussed earlier, we know that the sequence can only be integers, so in MySQL, we have the following integer types available.
bigint - It ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
int - It ranges from -2,147,483,648 to 2,147,483,647
decimal and numeric with a scale of 0
smallint - It ranges from -32,768 to 32,767
tinyint - It ranges from 0 to 255
Start with and Maximum value.
Then we need to initialize the sequence with the starting value corresponding to the START WITH section, and we also have to set the maximum value until the sequence increment.
Increment by
In the INCREMENT BY section, we have to write the value each sequence will get incremented.
Nocycle/Cycle
In, MySQL we have two options for the sequence, either to cycle it, which means to start again from the beginning when the sequence value is up to the maximum value, or to use nocycle, in which sequence will end when it reaches the maximum value.
Nocache/Cache
As the name suggests, the MySQL Cache command preallocates some sequences from the schema for faster access to the desired value. In contrast, if we use the Nocache command, it will not preallocate anything from the database.
Note:-If we omit the Nocache/Cache command. Oracle will preallocate 20 sequences by default.
We also have one more method to use sequences in MySQL with the help of the AUTO_INCREMENT field. Let us look at its syntax.
AUTO_INCREMENT Field
AUTO_INCREMENT field is the easiest way to implement sequences in MySQL.
By default, the starting value of the AUTO_INCREMENT field is one, and every time It gets incremented by 1 for each new record.
We can use this field with the help of the following syntax.
CREATE TABLE table_name
Serial_Id Int NOT NULL AUTO_INCREMENT,
First name varchar(255),
Primary key(Serial_Id);
We have successfully learned all the basic syntax of using MySQL sequences, and now we will look at some examples to enhance our understanding.
Caution
There are several servers for SQL, and every other server uses some different syntaxes for using sequences, so we need to be careful while generating sequences.
Examples
Let us see an example of creating a sequence named “serial_id,” which starts from one and goes up to the number of values inserted with a regular increment of one.
The following query will, first of all, create a table named candidates, and will declare its columns with the AUTO_INCREMENT function, then we will insert the values and see in the output whether the AUTO_INCREMENT function is creating an index or not and hence execute our requirements.
CREATE TABLE candidates
( SERIAL_ID int(10) NOT NULL AUTO_INCREMENT,
Fullname varchar(255)
);
INSERT INTO candidates (Fullname) values
(‘Ankit’),
(‘Arpit’),
(‘Suhana’);
SELECT * FROM candidates;
This query gives out the following output.
As we can see in the above output that the AUTO_INCREMENT Function is creating the index accordingly, the above-created sequence will start from one and have no limit on its maximum value and will not be repeated as we have used NO CYCLE.
Note:-The above query will only work only in the MySQL server, the sequences are generated with the help of the AUTO_INCREMENT field.
Note:-If we want to add sequences in decreasing order, we can increment them by a negative integer.
I hope you got the gist of using sequences as we come to the end of this blog.
Let us see some of the Frequently asked questions from this topic.
1.How can we initiate the AUTO_INCREMENT field in MySQL with other values?
We can do that with the help of ALTER Command. The following query will help you to do so
ALTER TABLE Candidates AUTO_INCREMENT=100;
2.What are nextval and currval functions in MySQL?
The currval returns the current value of the sequence, and the nextval increments the sequence and returns the next value of the sequence.
These two are essential functions in MySQL.
3.Do we need to follow different syntaxes for different servers?
Yes, As discussed earlier the syntax to use sequences in DBMS depends on the SQL server.
Let us now summarize our article in the following section.
Key takeaways
Long Blog? No worries, sometimes you need to dig deep into the soil searching for gold, and I hope you got what you were looking for.
In this article, we learned about using sequences in SQL. We saw Syntaxes using it on two different servers and some examples of creating sequences. In the end, we discussed some of the frequently asked questions and thus came to an end with the blog.
Do not stop now and access some of the essential study materials here Coding Ninjas Studio and also practice some critical questions here at Top-100-sql-problems.
85+ registered