{"id":26372,"date":"2018-08-26T09:06:18","date_gmt":"2018-08-26T09:06:18","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=26372"},"modified":"2021-03-11T18:28:15","modified_gmt":"2021-03-11T12:58:15","slug":"sql-sequence","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-sequence\/","title":{"rendered":"Create SQL Sequence – Syntax and Example"},"content":{"rendered":"

In this tutorial, we will learn about the Sequences in SQL. Let us now dive deep into SQL sequences and understand them in detail.<\/p>\n

What are Sequences in SQL?<\/h3>\n

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

With the help of sequences, we can provide identification for even the data which otherwise can\u2019t be identified using one parameter.<\/p>\n

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.<\/p>\n

In SQL for generating the sequences, we use Autoincrement.<\/p>\n

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.<\/p>\n

Why do we need SQL Sequences?<\/h3>\n

When we have data that doesn\u2019t provide unique identifiers, we create sequences that actually help us to create identification marks for each row.<\/p>\n

We can consider the situation when we have data that has some similar entries, in such cases, we need identification and for that we use sequences.<\/p>\n

Thus, creating sequences helps us to create identification points for each row or the tuple.<\/p>\n

What is Auto Increment in SQL?<\/h3>\n

The data which we store in our database does not always have unique properties to enable the identification of each record uniquely.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

We assign the auto-increment to the Primary Key attribute to ensure that the unique identity of data is maintained.<\/p>\n

Syntax:<\/strong><\/p>\n

1. To create an Auto Increment Column.<\/p>\n

CREATE TABLE tableName (\r\ncol1 NOT NULL AUTO_INCREMENT,\r\nCol2, col3,......\r\nPRIMARY KEY(col1));\r\n<\/pre>\n
2. To alter the starting point of the Auto Increment Column.<\/p>\n
ALTER TABLE tableName AUTO_INCREMENT = n;\r\n<\/pre>\n
3. To alter the amount of increment of each column.<\/p>\n
CREATE TABLE tableName (\r\ncol1 NOT NULL AUTO_INCREMENT(n,m),\r\nCol2, col3,......\r\nPRIMARY KEY(col1)\r\n);\r\n<\/pre>\n
Where n is the starting point and m is the gap between each created record.<\/p>\n
Features of Auto Increment in SQL<\/h3>\n
Some of the features of Auto Increment are as follows:<\/p>\n
1. Allows us to create Primary Key in data that doesn\u2019t have any unique identification attribute.<\/p>\n
2. We can set the starting value explicitly and modify the same at any particular time.<\/p>\n
3. Helps us to create unique identification in the records.<\/p>\n
4. Provides us with the flexibility to handle the gap between each record.<\/p>\n
5. We can leave the attribute set to auto-increment empty as it will automatically take its values.<\/p>\n
Steps to create SQL Auto Increment Attribute<\/h3>\n
1. Let us create a new database called DataFlair_emp with the following columns.<\/strong><\/p>\n