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
CREATE SEQUENCE sequence-name START WITH initial-value INCREMENT BY increment-value MAXVALUE maximum-value CYCLE | NOCYCLE;
- 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.
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:
Query to insert a record into the Student table:
INSERT INTO Student VALUE(seq_1.nextval, '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
Please follow me to read my latest post on programming and technology if you like my post.
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.