{"id":24642,"date":"2018-08-15T07:00:23","date_gmt":"2018-08-15T07:00:23","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=24642"},"modified":"2021-03-11T21:05:55","modified_gmt":"2021-03-11T15:35:55","slug":"sql-auto-increment","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-auto-increment\/","title":{"rendered":"Learn SQL Auto Increment Field With Syntax"},"content":{"rendered":"

In this tutorial, we will discuss auto increment in SQL. Let us now dive deep into the concept, syntax and examples of Auto Increment in SQL.<\/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 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.<\/strong><\/p>\n

CREATE TABLE tableName (\r\ncol1 NOT NULL AUTO_INCREMENT,\r\nCol2, col3,......\r\nPRIMARY KEY(col1)\r\n);\r\n<\/pre>\n
2. To alter the starting point of the Auto Increment Column.<\/strong><\/p>\n
ALTER TABLE tableName AUTO_INCREMENT = n;\r\n<\/pre>\n
3. To alter the amount of increment of each column.<\/strong><\/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 SQL Auto Increment<\/h3>\n
Some of the features of Auto Increment are as follows:<\/p>\n
1. Allows us to create Primary Key in data which 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 an Auto Increment Attribute<\/h3>\n
1. Let us create a new database called DataFlair_emp with the following columns.<\/strong><\/p>\n