{{Short description, Field in databasesAn identity column is a column (also known as a
field
Field may refer to:
Expanses of open ground
* Field (agriculture), an area of land used for agricultural purposes
* Airfield, an aerodrome that lacks the infrastructure of an airport
* Battlefield
* Lawn, an area of mowed grass
* Meadow, a grass ...
) in a
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
that is made up of values generated by the database. This is much like an AutoNumber field in
Microsoft Access
Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Access Database Engine (ACE) with a graphical user interface and software-development tools (not to be confused with the old Microsoft Access ...
or a
sequence
In mathematics, a sequence is an enumerated collection of objects in which repetitions are allowed and order matters. Like a set, it contains members (also called ''elements'', or ''terms''). The number of elements (possibly infinite) is calle ...
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
science
Science is a systematic endeavor that Scientific method, builds and organizes knowledge in the form of Testability, testable explanations and predictions about the universe.
Science may be as old as the human species, and some of the earli ...
, many RDBMS systems implement some type of generated key, although each has its own terminology. Today a popular technique for generating identity is to generate a random UUID.
An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.
It is a common misconception that an identity column will enforce uniqueness; however, this is not the case. If you want to enforce uniqueness on the column you must include the appropriate constraint too.
In
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
you have options for both the seed (starting value) and the increment. By default the seed and increment are both 1.
Code Samples
Create Table Contacts (
FirstName varChar(30),
LastName varChar(30),
Phone varChar(16),
ContactID int identity(1, 1)
)
or
Create Table Contacts (
FirstName varChar(30),
LastName varChar(30),
Phone varChar(16)
)
GO
Alter Table Contacts Add ContactID int identity(1, 1)
In Postgres
CREATE TABLE contact
(
contact_uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
first_name varchar,
last_name varchar,
phone varchar
);
Related functions
It is often useful or necessary to know what identity value was generated by an INSERT command.
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
provides several functions to do this: @@IDENTITY provides the last value generated on the current connection in the current scope, while IDENT_CURRENT(''tablename'') provides the last value generated, regardless of the connection or scope it was created on.
Example:
Insert Into Contacts ( FirstName, LastName ) Values ( 'Test', 'User' )
--
Select @@Identity
-- OR --
Declare @ID int
Select @ID = @@Identity
Update Contacts Set Phone = 'XXX-YYY-ZZZZ' Where ContactID = @ID
See also
*
Surrogate key
A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical key) in a database is a unique identifier for either an ''entity'' in the modeled world or an ''object'' in the database. The surrogate key is ''not'' deri ...
*
Unique key In relational database management systems, a unique key is a candidate key that is not the primary key of the relation. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the prim ...