SQL IDENTITY COLUMN Vs PRIMARY KEY Constraint
In this article, we'll look at the distinctions between the
IDENTITY column and
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 :-
|IDENTITY COLUMN||PRIMARY KEY|
|The Identity column is incremented automatically.||A primary key is a column that is unique. The user will enter the value for the primary key.|
|The values of the Identity column cannot be changed.||The values of primary keys can be changed.|
|The Identity column is a main key in all cases.||None of the primary keys are identity columns.|
|Each table can only have oneidentity column.||A table can only have one primary key, which can be made up of one or more fields.|
|Only incremented numeric values are allowed.||Increment are allowed.|
|Can be used as a foreign key in other tables.||Can be used as a foreign key in other tables.|