SQL IDENTITY COLUMN Vs PRIMARY KEY Constraint

In this article, we'll look at the distinctions between the IDENTITY column and PRIMARY KEY.

SQL IDENTITY COLUMN Constraint

The IDENTITY property is used to generate an identity column for a table as follows:

Example: The default seed and increment values are 1 and 1, respectively (1,1). It indicates that the first row in the table will have a value of one.

The IDENTITY property is used to create a new table for the personal identification number column in the following statement:

CREATE TABLE Person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
 Gender CHAR(1) NOT NULL
);

INSERT INTO Person VALUES(201,'Harinarayan','M'),
INSERT INTO Person VALUES(203,'Venkat','M'),
INSERT INTO Person VALUES(205,'Banu','F');
SELECT* FROM Person;

person_id is the identity column with a seed value of 1 and an increment values of 1. The identity value for the initial row added into a table is determined by the seed value. The identity value of consecutive rows entered into the table is determined by the increment value.

SQL PRIMARY KEY Constriant

A PRIMARY KEY is a column (or set of columns) in a table that uniquely identifies each row.

The PRIMARY KEY constraint is used to generate a primary key for a table.

There can only be one primary key per table. All columns included in the main key must be marked as NOT NULL. If the NOT NULL constraint is not provided for any of the primary key columns, SQL Server applies it automatically.

Example: The following example generates a table with a single column PRIMARY KEY :

CREATE TABLE sales (
    customerid INT PRIMARY KEY IDENTITY,
    customername VARCHAR (255) NOT NULL,
    purchaseddate DATE NOT NULL
);

The customerid column is the PRIMARY KEY column in the Sales table. It indicates that the customerid column has unique values.

For the customerid column, the IDENTITY property is utilised to produce unique integer values automatically.

SQL DIFFERENCE BETWEEN IDENTITY COLUMN And PRIMARY KEY Constraint

Here, we will discuss the 6 main difference between identity column property & primary key :-