SQL Sequence

SQL Sequence

What are Sequences in SQL?

When we talk about sequences they are numerical series which helps us to identify each row of the table with a unique identity.

With the help of sequences, we can provide identification for even the data which otherwise can’t be identified using one parameter.

Using the series we can easily create single parameters which identify each row of our database. Examples could be Adhaar Card numbers of each citizen or roll numbers in a class.

In SQL for generating the sequences, we use Autoincrement.

With the help of autoincrement, we can generate sequences that are integers and can decide the starting and endpoint such that they always remain unique.

Why do we need SQL Sequences?

The data which we store in our database does not always have unique properties to enable the identification of each record uniquely.

To ensure that we can uniquely identify each record we need to create an external unique identification attribute, which enables us to identify each row.

We create an additional attribute of numeric type and set it to Auto Increment, ones set to auto-increment the values are automatically incremented and stored in the database.

This helps us to identify each record easily. We need to set a column as Auto Increment in the Create statement itself as otherwise, problems would arise.

We assign the auto-increment to the Primary Key attribute to ensure that the unique identity of data is maintained.

Syntax:

1. To create an Auto Increment Column.

   CREATE TABLE tableName(
   col1 NOT NULL AUTO_INCREMENT,
   Col2, col3,......
   PRIMARY KEY(col1));
   

2. To alter the starting point of the Auto Increment Column.

   ALTER TABLE tableName AUTO_INCREMENT = n;

3. To alter the amount of increment of each column.

   CREATE TABLE tableName(
   col1 NOT NULL AUTO_INCREMENT(n,m),
   Col2, col3,......
   PRIMARY KEY(col1)
   );
   

Where n is the starting point and m is the gap between each created record.

Features of Auto Increment in SQL

Some of the features of Auto Increment are as follows:

• Allows us to create Primary Key in data that doesn’t have any unique identification attribute.
• We can set the starting value explicitly and modify the same at any particular time.
• Helps us to create unique identification in the records.
• Provides us with the flexibility to handle the gap between each record.
• We can leave the attribute set to auto-increment empty as it will automatically take its values.

Steps to create SQL Auto Increment Attribute

1. Let us create a new database called IBM_emp with the following columns.

   • ID int Auto Increment NOT NULL
   • Name varchar(50)
   • Location varchar(50)

Query:

USE IBM;
CREATE TABLE IBM_emp(
ID int NOT NULL auto_increment,
Name varchar(50),
Location varchar(50),
PRIMARY KEY(ID));

2. Let us now insert data in our database.

   Query:

   USE IBM;
   INSERT INTO IBM_emp(Name, Location) VALUES
   ("Ajeet","Lucknow"),
   ("Ram","Noida"),
   ("Neha","Meerut"),
   ("Ravi","Bhopal"),
   ("Priya","Noida");
   SELECT * FROM IBM_emp;
   

3. Let us now alter the starting value of Autoincrement and then again insert some new records in our database.

   Query:

   USE IBM;
   ALTER TABLE IBM_emp AUTO_INCREMENT = 100 ;
   INSERT INTO IBM_emp(Name,Location) VALUES
   ("Neha","Meerut"),
   ("Ajeet","Lucknow");
   SELECT * FROM IBM_emp;