SQL Reference - IDENTITY

IDENTITY

IDENTITY is an attribute used in SQL to generate a unique, potentially auto-incrementing number for each new row inserted in a table. This IDENTITY attribute is specifically designed for creating primary keys that are numeric and automatically incremented. This attribute assists in decreasing redundancy and maintaining database efficiency.

Example

CREATE TABLE Employees (
    ID int IDENTITY(1,1),
    name varchar(255)
);

INSERT INTO Employees (name)
VALUES ('John Doe'), ('Jane Doe');

SELECT * FROM Employees;

Output

| ID |   name    |
|----|-----------|
| 1  | John Doe  |
| 2  | Jane Doe  |

Explanation

In the example above, the IDENTITY property is used while creating a table Employees. The IDENTITY property creates an identity column for the table Employees. An identity column in SQL Server is typically used for primary keys. When a new row is added, the ID value is automatically created by incrementing the last ID by 1, as the IDENTITY property is set to (1,1). Hence, when inserting the names ‘John Doe’ and ‘Jane Doe’, the ID(identity column) values are automatically created as 1 and 2 respectively.

Example

CREATE TABLE Employee (
  ID NUMBER GENERATED ALWAYS AS IDENTITY,
  Name VARCHAR2(100) NOT NULL
);
INSERT INTO Employee (Name)
VALUES ('John Doe');

Output

1 row created.

Explanation

In the example, a new table named “Employee” is created with two columns, “ID” and “Name”. The “ID” column is defined as an identity column, this means that Oracle generates a unique value for each new row inserted. The “Name” column is filled manually. When inserting a name into the table, Oracle automatically provides a unique ID for the “ID” column.