Why is use the IDENTITY column in the insert statement in SQL?
Why is use the IDENTITY column in the insert statement in SQL?
Ask by Ashutosh Patel
Updated 22 Mar 2023
The IDENTITY column is used in the INSERT statement in SQL to generate a unique value for a particular column in a table. When a new row is inserted, the IDENTITY column will automatically assign a new and unique value for that column. The user doesn't have to manually input a unique value and check if each row in the table has a unique identifier.
They are used as a primary key, a foreign key, a timestamp, or a sequence number for auditing purposes. The IDENTITY column is completely automated and saves time and reduces the likelihood of errors in the data.
SYNTAX:
IDENTITY [( seed, increment)]
Seed: Seed is the initial value of the column. Its default value is 1.
Increment: Increment is the value that is added to the identity value of the previous row and its default value is 1.
Given below is an example demonstrating the use of the IDENTITY column:
CREATING THE TABLE:
In this example, we are creating a table called "Customers" with three columns. The "CustomerID" column is defined as an IDENTITY column with a seed value of 2 and an increment of 2. It acts as the primary key and generates a unique key for each row, starting by 2 and incrementing by 2 for each new row.
INSERTING VALUES:
In this example, we left the "CustomerID" column empty because it generates a unique value automatically. The database will add a new value for this column when we execute the INSERT statement. Shown below is a resulting table of the above-mentioned commands: