This content originally appeared on DEV Community and was authored by Pranav Bakare
A sequence in SQL, particularly in databases like Oracle, is a database object that generates a series of unique numeric values in a specified order. Sequences are commonly used for automatically generating unique primary key values for tables, ensuring that each value is distinct and follows a sequential order (incremented or decremented).
Key Features of a Sequence:
Unique: Sequences generate unique numbers, making them suitable for auto-incrementing primary keys.
Automatically Incremented: Each time the sequence is accessed (via NEXTVAL), it generates the next value in the defined sequence.
Customizable: You can specify the starting point, increment value, minimum and maximum values, and whether the sequence should cycle after reaching the maximum value.
Not Tied to a Specific Table: Sequences are independent of tables, so they can be used in any table or for other purposes.
Components of a Sequence:
START WITH: Defines the starting point of the sequence (e.g., start with 1).
INCREMENT BY: Specifies how much the sequence value will increase or decrease (e.g., increment by 1 or by 5).
MINVALUE / MAXVALUE: Sets the minimum or maximum limits for the sequence.
CYCLE / NOCYCLE: Determines whether the sequence should restart (cycle) after reaching the maximum value or throw an error.
CACHE / NOCACHE: Caches sequence values in memory for faster access or generates them without caching.
Syntax for Creating a Sequence:
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE min_value
MAXVALUE max_value
CYCLE/NOCYCLE
CACHE/NOCACHE;
Example of Creating a Sequence:
CREATE SEQUENCE emp_seq
START WITH 1 -- Start from 1
INCREMENT BY 1 -- Increment by 1 for each new value
MINVALUE 1 -- Minimum value is 1
MAXVALUE 9999 -- Maximum value is 9999
NOCYCLE -- Do not restart after reaching the max value
CACHE 20; -- Cache 20 values for performance
Using a Sequence in SQL:
To retrieve the next value of a sequence, you use the NEXTVAL function. You can also use the CURRVAL function to retrieve the current value of the sequence.
Using NEXTVAL in an INSERT Statement:
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');
This query will insert a new employee and automatically assign a unique employee_id generated by the sequence.
Using CURRVAL:
SELECT emp_seq.CURRVAL FROM dual;
This will return the current value of the sequence, which is the value most recently generated by NEXTVAL in the current session.
Example Scenario:
If you want to auto-generate unique employee IDs for a table of employees, you would:
Create a sequence that starts at 1 and increments by 1.
Use the sequence's NEXTVAL each time you insert a new employee.
Managing Sequences:
Altering a Sequence: You can modify an existing sequence if needed.
ALTER SEQUENCE emp_seq
INCREMENT BY 2;
Dropping a Sequence: If you no longer need a sequence, you can drop it.
DROP SEQUENCE emp_seq;
Key Points:
Independent of Tables: Sequences are not tied to any specific table, so you can use them in multiple tables if required.
Thread-Safe: Sequences ensure thread safety by guaranteeing that each NEXTVAL call will return a unique value, even in a multi-user environment.
In summary, a sequence in SQL is a powerful tool for generating unique values automatically, commonly used for primary keys and ensuring data integrity across records.
This content originally appeared on DEV Community and was authored by Pranav Bakare
Pranav Bakare | Sciencx (2024-10-08T19:25:49+00:00) sequence in SQL | PART 1. Retrieved from https://www.scien.cx/2024/10/08/sequence-in-sql-part-1/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.