SQL - Using Sequences

The sequences in SQL is a database object that generates a sequence of unique integer values. They are frequently used in databases because many apppcations require each row in a table to contain a unique value and sequences provide an easy way to generate them. Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2.

A sequence is created using the CREATE SEQUENCE statement in SQL. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence.

Syntax

Following is the syntax to create a sequence in SQL −

CREATE SEQUENCE Sequence_Name
START WITH Initial_Value
INCREMENT BY Increment_Value
MINVALUE Minimum_Value
MAXVALUE Maximum_Value
CYCLE|NOCYCLE;

Here,

Sequence_Name − This specifies the name of the sequence.

Initial_Value − This specifies the starting value from where the sequence should start.

Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively.

Minimum_Value − This specifies the minimum value of the sequence.

Maximum_Value − This specifies the maximum value of the sequence.

Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning.

Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value.

Example

First of all, let us try to create a table “STUDENTS” using the following query −

SQL> CREATE TABLE STUDENTS (ID INT, NAME CHAR(20), AGE INT NOT NULL);

Now, let us insert some records in the table using INSERT statements as shown in the query below −

SQL> INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Dhruv ,  20 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Arjun ,  23 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Dev ,  25 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Riya ,  19 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Aarohi , 24 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Lisa ,  20 );
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL,  Roy ,  24 );

Let’s verify whether the table STUDENTS is created or not using the following query −

SQL> SELECT * FROM STUDENTS;

The table is successfully created in the SQL database.

+------+-----------+------+
|  ID  |     NAME  |  AGE |
+------+-----------+------+
| NULL |    Dhruv  |   20 |
| NULL |    Arjun  |   23 |
| NULL |    Dev    |   25 |
| NULL |    Riya   |   19 |
| NULL |    Aarohi |   24 |
| NULL |    Lisa   |   20 |
| NULL |    Roy    |   24 |
+------+-----------+------+

Now, let us try to create a sequence in SQL using the following statement −

SQL> CREATE SEQUENCE My_Sequence AS INT
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 5
   CYCLE;

In the above query, the sequence is named “My_Sequence” and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value.

Once the sequence is created, it can be used to generate unique integer values. Now, let us try to update the data in the “ID” column of the STUDENTS table using the following query −

SQL> UPDATE STUDENTS SET ID = NEXT VALUE FOR My_Sequence;

Output

When you execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Let us verify whether is sequence is updated in the ID column of the table or not using the following query −

SQL> SELECT * FROM STUDENTS;

+------+-----------+------+
|  ID  |     NAME  |  AGE |
+------+-----------+------+
|    1 |    Dhruv  |   20 |
|    2 |    Arjun  |   23 |
|    3 |    Dev    |   25 |
|    4 |    Riya   |   19 |
|    5 |    Aarohi |   24 |
|    1 |    Lisa   |   20 |
|    2 |    Roy    |   24 |
+------+-----------+------+

Using AUTO_INCREMENT column

The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave the rest to MySQL to take care.

Example

Try out the following example. This will create a table and after that it will insert a few rows in this table where it is not required to give a record ID because its auto-incremented by MySQL.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
   -> (NULL, housefly , 2001-09-10 , kitchen ),
   -> (NULL, milppede , 2001-09-10 , driveway ),
   -> (NULL, grasshopper , 2001-09-10 , front yard );
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duppcates: 0  Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | milppede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values

The LAST_INSERT_ID( ) is an SQL function, so you can use it from within any cpent that understands how to issue SQL statements. Otherwise PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.

PERL Example

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle.

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES( moth , 2001-09-14 , windowsill )");
my $seq = $dbh->{mysql_insertid};

PHP Example

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calpng the mysql_insert_id( ) function.

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES( moth , 2001-09-14 , windowsill )", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick, but you should be very careful to do this and check if your table is having a join with another table or not.

If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.

The following example shows how to renumber the id values in the insect table using this technique.

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value

By default, MySQL will start the sequence from 1, but you can specify any other number as well at the time of table creation.

The following code block has an example where MySQL will start sequence from 100.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;