HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Is it recommended to use Identity as an alternative to primary key?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
identityalternativeprimaryrecommendedusekey

Problem

We can declare an Identity like id_num so that id_num will have an increment of unique numbers.

CREATE TABLE new_employees
(
  id_num int IDENTITY(1,1),
  fname varchar (20),
  minit char(1),
  lname varchar(30)
)


Is it recommended to use Identity as a alternative to Primary key since Identity provided a unique number for each row?

Solution

Identity columns and Primary Keys are two very distinct things. An Identity column provides an auto-incrementing number. That's all it does. The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key. Again in MS SQL Server it is also an index (in some RDBMS they are not as closely tied). As an index it provides faster lookups etc. Frequently Identity columns are used as the Primary Key if no good natural key exists, but are not a substitute.

Context

StackExchange Database Administrators Q#46373, answer score: 30

Revisions (0)

No revisions yet.