SQL Auto Increment
Databases are used to store the humongous amount of data logically. You might have come across various instances wherein you face difficulty mentioning a unique number for every record present in the table. This scenario is practically impossible because the manual entry is restricted. Thus, there's no scope for incrementing the values. In this kind of situation, you drop in other methods and choose auto increment in SQL. The auto-increment in SQL entering a unique number increases the count to every new record present in the table automatically. To get familiar with auto-increment in SQL, let's look at some justifications to understand better.
What is an auto-increment in SQL?
As the name suggests, auto-increment is defined as a field that is mainly used to generate a unique number for each record being added to a table at any instance. In general terms, it is used for a primary key column because a primary key in SQL is supposed to be unique and not null. Thus, auto-increment makes it easy for the developers to automatically generate a unique number for all the records.
The data that you store in the database will not always have unique properties, so that some records are picked and manipulated uniquely. To ensure such unique identification of each record, you create an attribute for unique identification that can specify each row. Thus, after creating such a unique identity, you need to set it with auto increment so that values once incremented can be conveniently stored in the database.
Auto increment in SQL adds up an advantage by easily identifying each record. It is important to keep in mind that you need to set the column as auto-increment in the Create statement. Else, some problems may arise. Thus, to maintain integrity, you need to assign a primary key to the auto-increment, ensuring that the data's identity is maintained in the database.
Now, since you are quite familiar with auto-increment in SQL, let's proceed with discussing this field in different Database Management Systems.
Auto Increment features in SQL.
Some of the features of auto-increment are enlisted below:
- It helps you to create Primary Key which might not have any unique identification attribute in data.
- The value in auto-increment can be explicitly initialized and modified at any point in time.
- Unique identification of records can be easily created.
- With Auto Increment, flexibility to process the gap between each record can be handled easily.
- Writing queries in SQL for auto-increment is syntactically easy.
Setting up Auto Increment
For your convenience, you can start creating a table having certain attributes like CustomerID, CustomerName, Age, PhoneNumber, etc. To use the auto-increment field, you need to use the IDENTITY keyword in your SQL Server. To proceed with the setup, let's start with the syntax and examples related to auto increment.
Syntax
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);
SQL Server Example
Suppose you must create a table following the above syntax having attributes like Customers as name and columns having CustomerID, CustomerName, Age, and PhoneNumber. In this case, if you create an auto-increment, the CustomerID will be treated as the primary key because it can act as a unique identifier for the table.
CustomerID int IDENTITY(1,1) PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);
In the above example, the initial value for the IDENTITY has been granted one, and it is expected that this value is increased by one whenever a new record is added. You can directly mention the value according to your need, and to insert the value, you need to use the INSERT query in the following way, as shown below.
VALUES ('Kundan','24','9876543210');
If you observe closely, CustomerID columns aren't mentioned since they will get generated automatically. So, if you want to store more such values, you can do it in the following way.
VALUES ('Suraj','21','9812323210');
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Anup','19','9954413210');
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Monalisa','24','9876544911');
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Sanju','31','9657154310');
After including the above data and saving changes, the result might look like this.
So far, you have seen an elaborated version of Auto Increment in SQL. Now, you would see a fast-paced recap of some of the important actions and examples that can be performed to get a clear idea about auto-increment and its diversified applications.
Creating an auto-increment column
col1 NOT NULL AUTO_INCREMENT,
Col2, col3,......
PRIMARY KEY(col1)
);
Altering auto-increment column
Amount of increment of each column
col1 NOT NULL AUTO_INCREMENT(n,m),
Col2, col3,......
PRIMARY KEY(col1)
);
Here, n and m are starting points and gaps between the created record.
Creating Auto Increment Attribute
First of all, you need to create a sample database with any desired name. For convenience, you create a database name as DataFlair_emp with the following columns shown below.
- ID int Auto Increment NOT NULL
- Name varchar(50)
- Location varchar(50)
Use the following query to outlay the above-applied columns.
CREATE TABLE DataFlair_emp(
ID int NOT NULL auto_increment,
Name varchar(50),
Location varchar(50),
PRIMARY KEY(ID));
The results might appear something like this.
You can see that the database has been created with an ID defined as auto-increment. The next task you are assigned to do is insert data into the database using the query given below.
INSERT INTO DataFlair_emp(Name, Location) VALUES
("Amit","Indore"),
("RAJ","Pune"),
("Rita","Noida"),
("Shiv","Indore"),
("Ram","Noida");
SELECT * FROM DataFlair_emp;
In the above query, you primarily entered the data despite passing any numerical value or ID column value. It is automatically handled by auto-increment in SQL, and the results are automatically assigned with their respective values.
The expected output to visualize the database can be seen through the image given below.
The next task is to find a way to alter the table. Previously, you had seen a code snippet on how to alter an SQL table, and now you would be practically seeing through this example.
ALTER TABLE DataFlair_emp AUTO_INCREMENT = 100 ;
INSERT INTO DataFlair_emp(Name,Location) VALUES
("Riya","Indore"),
("Shriya","Pune");
SELECT * FROM DataFlair_emp;
The output of the above code snippet would appear something like this.
You can note that the auto-increment value has been set to 100 initially for inserting multiple records in the results. You can add such values ranging upto 100, and they will be incremented by one and stored as the primary key in our database.
Examples of Auto Increment
Example 1
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Output
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+--------+
In the above example, there exists no specified value for the AUTO_INCREMENT column. MySQL automatically assigns the number automatically and explicitly, ranging from 0 to the number of generated column sequences. For instance, consider the below format.
If you assigned the column NOT NULL, there's a possibility to assign it to the columns that can generate sequence numbers.
For this type of case, inserting any other value for the auto-increment column might get reset automatically, and the set values follow a sequence from the larger columns. For example:
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | groundhog |
| 8 | squirrel |
| 100 | rabbit |
| 101 | mouse |
+-----+-----------+
When you update an existing auto-increment column value in any database, the table directly doesn't reset the sequence of auto-increment. You can therefore retrieve the most recent value that came across auto increment or is inserted as last. These functions are connection-specific, and due to this property, there is no effect on other insert operations being carried.
Another point to add here is that the auto-increment can easily hold the maximum sequence for larger values. However, in this process of reaching the upper limit of the data type, the generated sequence number might fail due to the maximum limit. For instance, if your maximum permissible sequence is set to 127 for an integer, the same unsigned might have the sequence set to 255. Thus, you must initially keep the conventions to TINYINT, BIGINT, etc.
Summary
In this tutorial, you saw what an auto-increment attribute in SQL is and how to use it to store, sequential, and retrieve data. In the later sections, you became familiar with the syntax and plenty of examples to understand how the flow of auto-increment works for different instances, be it inserting or altering the values in the table. The auto-increment function allows you to create primary keys based on the fact that your data lacks some unique identification. With such features and functionalities, auto-increment allows you to modify and bridge the gap between the primary key values.
