SQL Sequence:




A sequence is a sequence of integers that is used to provide values on demand in order. The sequence is used to assign a unique value to each row or each column in a table. Sequences are widely used in database management systems but MySQL supports AUTO_INCREMENT.




The AUTO_INCREMENT is used because it automatically increments the value of a column or a row by one. The user only has to define the column using AUTO_INCREMENT and then MySQL will do the rest. The increment is done when an entry is made in the table.

That is each time a value is entered into a column or the row of the table then the value is incremented by 1. The sequence and AUTO_INCREMENT are the same but sequence supports some advanced features as well.

 

Creating Sequence

The syntax of creating a sequence is given below:

Create sequence sequence-name
Start with initial-value
Increment by increment-value
Maxvalue maximum-value
Cycle| nocycle

In the above syntax, the initial value is from where the sequence will start. The increment value is used to specify the value by which the value of sequence will be incremented.

The Maxvalue identifies that value to which the sequence will be incremented. The sequence cannot be more than the Maxvalue. Cycle is used to indicate that if the value of sequence is exceeded from the Maxvalue then the cycle will restart from the beginning of the sequence. The no cycle indicates that if the sequence reaches more than the max value then an error will be thrown.

 

EXAMPLE to Create Sequence

Suppose you want to create a sequence that is named as SEQ1 and you want to initialize this sequence at 1 and also want to increment the value of the sequence by 1 and you want to set the maximum value to be 99. Then you will use the following query:

Create sequence SEQ1

Start with 1

Increment by 1

Maxvalue 99

Cycle;

The above query will create the desired sequence with a maximum value of 99.




EXAMPLE to use Sequence

Suppose you have the following table named as student:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Now suppose you want to insert a new record in the table of a new student. Then you will use the following query:

Insert into Student value (SEQ1.nextval, ‘Bill’, ‘Finance’, 1100, ‘INVESTMENT’, 20/06/97)

The above query will generate the following result:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97
1 Bill Finance 1100 INVESTMENT 20/06/97




When the nextval is used the sequence will be incremented itself whether the user enters any record or not.