SQL - Using Sequences
Sequences in SQL are database objects that generate a sequence of unique
integer values. They are frequently used in databases because many
applications require that each row in a table must contain unique values 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.
MySQL does not support the CREATE SEQUENCE statement to create
sequences for table rows or columns. Instead, we can use AUTO_INCREMENT
attribute.
Sequences in MySQL
In MySQL, we use the AUTO_INCREMENT attribute to generate unique
integer values (sequences) for a column. By default, the sequence starts with
an initial value of 1 and increments by 1 for each new row.
Syntax
Following is the syntax of AUTO_INCREMENT attribute in MySQL −
CREATE TABLE table_name (
column1 datatype AUTO_INCREMENT,
column2 datatype,
column3 datatype,
...
columnN datatype
);
Example
In the following example, we are creating a table named CUSTOMERS. In
addition to that, we are defining AUTO_INCREMENT on ID column of the table.
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Here, we are adding some records into the above created table –
INSERT INTO CUSTOMERS VALUES
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);
The table will be created as follows −
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00
As we can see in the above table, the values in the ID column are auto
incremented.
Starting a Sequence at a Particular Value in MySQL
By default, MySQL sequences start from 1. To start a sequence with a
different value, we use the AUTO_INCREMENT in combination with
the ALTER statement.
Syntax
Following is the syntax to start the sequence with different value −
ALTER TABLE table_name AUTO_INCREMENT = value;
In the following query, we are creating a table named BUYERS with
AUTO_INCREMENT defined on the ID column.
CREATE TABLE BUYERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Here, we are making the sequence start with 100 using the ALTER Statement
as shown below −
ALTER TABLE BUYERS AUTO_INCREMENT=100;
Now, we are adding records into the BUYERS table using the INSERT INTO
statement −
INSERT INTO BUYERS VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);
The table will be created as −
ID NAME AGE ADDRESS SALARY
100 Ramesh 32 Ahmedabad 2000.00
101 Khilan 25 Delhi 1500.00
102 Kaushik 23 Kota 2000.00
103 Chaitali 25 Mumbai 6500.00
104 Hardik 27 Bhopal 8500.00
105 Komal 22 Hyderabad 4500.00
106 Muffy 24 Indore 10000.00
As observed in the table above, the values in the "ID" column begin with 100
instead of 1.
Sequences in SQL Server
In SQL server, a sequence can be created using the CREATE
SEQUENCE statement. The statement specifies the name of the sequence,
the starting value, the increment, and other properties of the sequence.
