Identity in SQL - sql - sql tutorial - learn sql
- IDENTITY is used in Microsoft SQL Server to automatically insert numerical primary key values to a table as new data is inserted.
- This is similar to the AUTO INCREMENT command in MySQL.
Syntax:
- The syntax for IDENTITY is as follows:
CREATE TABLE TABLE_NAME
(PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY ( [Initial_Value], [Interval] ),
...);
- where [Initial_Value] is the first value of the primary key, and [Interval] is the interval between two consecutive identity values.
- If no [Initial_Value] or [Interval] is specified, the default for both is 1.
- In other words, the primary key for the first row would be 1, and subsequent rows would get a primary key value that is 1 larger than the previous row.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample:
Example:
- Assume we want to create a table that consists of a primary key, last name, and first name. We use the following SQL:
CREATE TABLE USER_TABLE
(Userid int PRIMARY KEY IDENTITY(2,1),
Last_Name nvarchar(50),
First_Name nvarchar(50));
- Upon creation, the table is empty.
- We insert the first value:
INSERT INTO USER_TABLE VALUES ('Washington', 'George');
- Now the table has the following values:
Table USER_TABLE
| Userid | Last_Name | First_Name |
|---|---|---|
| 2 | Washington | George |
- Userid is 2 because we had specified the initial value to be 2.
- Next, we insert the second value:
INSERT INTO USER_TABLE VALUES ('Jefferson', 'Thomas');
- Now the table has the following values:
Table USER_TABLE
| Userid | Last_Name | First_Name |
|---|---|---|
| 2 | Washington | George |
| 3 | Jefferson | Thomas |
- User id for the second row is 3 because it is 1 larger than the previous User id, which is 2.