![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - Using Sequences
The sequences in SQL is a database object that generates a sequence of unique integer values. They are frequently used in databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them. Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2.
A sequence is created using the CREATE SEQUENCE statement in SQL. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence.
Syntax
Following is the syntax to create a sequence in SQL −
CREATE SEQUENCE Sequence_Name START WITH Initial_Value INCREMENT BY Increment_Value MINVALUE Minimum_Value MAXVALUE Maximum_Value CYCLE|NOCYCLE;
Here,
Sequence_Name − This specifies the name of the sequence.
Initial_Value − This specifies the starting value from where the sequence should start.
Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively.
Minimum_Value − This specifies the minimum value of the sequence.
Maximum_Value − This specifies the maximum value of the sequence.
Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning.
Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value.
Example
First of all, let us try to create a table “STUDENTS” using the following query −
SQL> CREATE TABLE STUDENTS (ID INT, NAME CHAR(20), AGE INT NOT NULL);
Now, let us insert some records in the table using INSERT statements as shown in the query below −
SQL> INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Dhruv', '20'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Arjun', '23'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Dev', '25'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Riya', '19'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Aarohi','24'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Lisa', '20'); INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Roy', '24');
Let’s verify whether the table STUDENTS is created or not using the following query −
SQL> SELECT * FROM STUDENTS;
The table is successfully created in the SQL database.
+------+-----------+------+ | ID | NAME | AGE | +------+-----------+------+ | NULL | Dhruv | 20 | | NULL | Arjun | 23 | | NULL | Dev | 25 | | NULL | Riya | 19 | | NULL | Aarohi | 24 | | NULL | Lisa | 20 | | NULL | Roy | 24 | +------+-----------+------+
Now, let us try to create a sequence in SQL using the following statement −
SQL> CREATE SEQUENCE My_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE;
In the above query, the sequence is named “My_Sequence” and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value.
Once the sequence is created, it can be used to generate unique integer values. Now, let us try to update the data in the “ID” column of the STUDENTS table using the following query −
SQL> UPDATE STUDENTS SET ID = NEXT VALUE FOR My_Sequence;
Output
When you execute the above query, the output is obtained as follows −
Commands completed successfully.
Verification
Let us verify whether is sequence is updated in the ID column of the table or not using the following query −
SQL> SELECT * FROM STUDENTS; +------+-----------+------+ | ID | NAME | AGE | +------+-----------+------+ | 1 | Dhruv | 20 | | 2 | Arjun | 23 | | 3 | Dev | 25 | | 4 | Riya | 19 | | 5 | Aarohi | 24 | | 1 | Lisa | 20 | | 2 | Roy | 24 | +------+-----------+------+
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.
Example
Try out the following example. This will create a table and after that it will insert a few rows in this table where it is not required to give a record ID because its auto-incremented by MySQL.
mysql> CREATE TABLE INSECT -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO INSECT (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), -> (NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM INSECT ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec)
Obtain AUTO_INCREMENT Values
The LAST_INSERT_ID( ) is an SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.
PERL Example
Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle.
$dbh->do ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')"); my $seq = $dbh->{mysql_insertid};
PHP Example
After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling the mysql_insert_id( ) function.
mysql_query ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')", $conn_id); $seq = mysql_insert_id ($conn_id);
Renumbering an Existing Sequence
There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick, but you should be very careful to do this and check if your table is having a join with another table or not.
If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.
The following example shows how to renumber the id values in the insect table using this technique.
mysql> ALTER TABLE INSECT DROP id; mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> ADD PRIMARY KEY (id);
Starting a Sequence at a Particular Value
By default, MySQL will start the sequence from 1, but you can specify any other number as well at the time of table creation.
The following code block has an example where MySQL will start sequence from 100.
mysql> CREATE TABLE INSECT -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected );
Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.
mysql> ALTER TABLE t AUTO_INCREMENT = 100;