While working with vast databases in SQL, several tables and data fields will require unique numbers. For instance, a column of the table with a PRIMARY KEY or UNIQUE constraint will always require a new number. You might be able to do it manually to a limited extent. But, when it comes to enormous databases, you might forget the last unique number you entered, or merely include the same number twice as it isn’t easy to remember everything. Besides the memory issue, providing a unique number to all the records is also a tedious task. That’s where auto increment in SQL comes in to play its part.
What is Auto Increment in SQL?
The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add. However, it can also be used for the UNIQUE constraint columns.
How to Set Up Auto Increment in SQL?
Now, since you know what the SQL auto increment field does, let’s set it up to automatically generate unique numbers for the records you enter in the database table. Several databases support SQL auto increment fields. You will be going through some primary DBMS systems and look at how to set up an auto increment column. You will have to create a Students table in all the DBMS and set auto increment in SQL.
Auto Increment in SQL: Setup for MySQL
In the MySQL server, it applies an auto increment field with the keyword AUTO_INCREMENT. By default, it starts with the number one and increases the value by one for each new record. In the example below, you will use the CREATE TABLE command to create a Students table and apply PRIMARY KEY and AUTO_INCREMENT to the ID column. After making the table, you will use the INSERT INTO command to add rows without providing the ID column’s value.
-- Creating table
CREATE TABLE Students(
ID int PRIMARY KEY AUTO_INCREMENT,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Age int
);
-- Inserting values
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);
-- Fetching values
SELECT * FROM Students;
Output:
As you can see in the output, auto increment in SQL provided the ID column with unique numbers as expected.
You can also make an auto increment in SQL to start from another value with the following syntax:
ALTER TABLE table_name AUTO_INCREMENT = start_value;
In the syntax above:
- start_value: It is the value from where you want to begin the numbering
Use the syntax to start the numbering from 100 in the Students table and then add more rows to see it in action.
ALTER TABLE Students AUTO_INCREMENT = 100;
INSERT INTO Students (FirstName, LastName, Age) VALUES ('David', 'Tiwari', 26);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Hitesh', 'Patel', 29);
SELECT * FROM Students;
Output:
Auto Increment in SQL: Setup for SQL Server
The SQL Server uses the IDENTITY keyword to set auto increment with the following syntax while creating a table:
IDENTITY (initial_value, increment_value);
In the above syntax:
- initial_value: It is the value from where you want the numbering to begin
- increment_value: It is the increment between each auto-generated number
Let’s create the same Students table in the SQL Server and use the IDENTITY keyword to set auto increment. This time you will directly start with 1000 and have an interval of 5 between each row’s number.
-- Creating table
CREATE TABLE Students(
ID int IDENTITY(1000, 5) PRIMARY KEY,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Age int
);
-- Inserting values
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);
-- Fetching values
SELECT * FROM Students;
Output:
Auto Increment in SQL: Setup for MS Access
The AUTOINCREMENT keyword is used to set up auto increment in MS Access. The default starting and increment values for AUTOINCREMENT are 1 and 1, respectively. However, you can easily change it while setting the auto increment with the following syntax:
AUTOINCREMENT(start_value, increment_value);
In the above syntax:
- start_value: It is the value from where you want the numbering to begin
- increment_value: It is the increment between each auto-generated number
This time you will create a Students1 table in the example below with the MS Access server and set up auto increment in SQL with the same input values. In this case, you have to set the start_value to 200 and increment_value to 4.
-- Creating table
CREATE TABLE Students1(
ID int AUTOINCREMENT(200,4) PRIMARY KEY,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Age int
);
-- Inserting values
INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);
INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);
INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);
-- Fetching values
SELECT * FROM Students1;
Output:
Auto Increment in SQL: Setup for Oracle
To set up auto increment in Oracle, you will have to create a sequence object first. This object is used to generate a number sequence. Here’s how you can use the CREATE SEQUENCE statement in Oracle to create a sequence object for the Students' table.
CREATE SEQUENCE seq_Students
MINVALUE 1
START WITH 20
INCREMENT BY 2
CACHE 20;
The syntax above will create a sequence object named seq_Students that starts with 20 and increases by 2 with a cache of 20 (the number of sequence values stored in memory). You will now use the built-in nextval function to add values with the auto increment feature and see it in action. Let’s create the table Students and insert the values.
CREATE TABLE Students(
ID number(10) PRIMARY KEY,
FirstName varchar2(50) NOT NULL,
LastName varchar2(50),
Age number(10)
);
INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Rahul', 'Kumar', 24);
INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Aakash', 'Roy', 25);
INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Nick', 'Chopra', 23);
-- Fetching values
SELECT * FROM Students;
Output:
Auto Increment in SQL: Setup for PostgreSQL
In PostgreSQL, the SERIAL keyword is used to set up an auto increment column. SERIAL in PostgreSQL is a data type, like BIGSERIAL and SMALLSERIAL. But it works similar to the auto increment in SQL. However, you cannot set a starting value or the increment value of your choice explicitly. Here’s how you can create a table and use the SERIAL keyword to set auto increment in PostgreSQL.
-- Creating table
CREATE TABLE Students(
ID SERIAL PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT,
Age int
);
-- Inserting values
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);
INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);
-- Fetching values
SELECT * FROM Students;
Output:
Conclusion:
In this article, you learned everything about auto increment in SQL. You have gone through the use of auto increment and how to set it up in different DBMS servers. Knowing how and where to use auto increment will help you efficiently manage enormous databases, especially while working with PRIMARY KEY constraint tables. Do you have any questions related to this article on Auto Increment in SQL? In case you do, then please put them in the comments section. Our team will help you solve them.
Happy learning!