coderz.py

Keep Coding Keep Cheering!

SQL: Sequence

SQL Views

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:

IDNAME
1Rabecca
2 Varun
Student

Query to insert a record into the Student table:

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

Output:

IDNAME
1Rabecca
2 Varun
3John
Student

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Advertisement