SQL: Sequence

What is an SQL Sequence?

Database systems offer sequence to produce unique numeric values in a series according to defined standards.

• In SQL, a sequence is a user-defined schema-bound entity that generates a set or sequence of numbers with important ordering.
• Some database applications may demand that each table row include unique values. In many circumstances, sequences are useful for quickly generating values.

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 of.

Creating a Sequence

Syntax :

CREATE SEQUENCE sequence-name
    START WITH initial-value
    INCREMENT BY increment-value
    MAXVALUE maximum-value
    CYCLE | NOCYCLE;

where,

• sequence_name: Name of the sequence.
• initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.
• increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.
• minimum_value: Minimum value of the sequence.
• maximum_value: Maximum value of the sequence.
• cycle: When sequence reaches its set_limit it starts from the beginning.
• nocycle: An exception will be thrown if the sequence exceeds its max_value.

Example

CREATE SEQUENCE seq_1
start with 0
increment by 1
minvalue 0
maxvalue 99
cycle;

Now let’s insert a value to a Student table using the above-created sequence:

ID	NAME
1	Rabecca
2	Varun

Query to insert a record into the Student table:

INSERT INTO Student VALUE(seq_1.nextval, 'John');

Output:

ID	NAME
1	Rabecca
2	Varun
3	John

Note: If we use nextval, the sequence will continue to grow even if no records are added to the table.

Note: also read about SQL: SET Operations

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py