Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

identity column in Sql server

Tags:

sql

sql-server

Why does Sql server doesn't allow more than one IDENTITY column in a table?? Any specific reasons.

like image 941
Bhaskar Avatar asked May 20 '09 19:05

Bhaskar


2 Answers

Why would you need it? SQL Server keeps track of a single value (current identity value) for each table with IDENTITY column so it can have just one identity column per table.

like image 131
mmx Avatar answered Sep 29 '22 02:09

mmx


An Identity column is a column ( also known as a field ) in a database table that :-

  1. Uniquely identifies every row in the table
  2. Is made up of values generated by the database

This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.

An identity column differs from a primary key in that its values are managed by the server and ( except in rare cases ) can't be modified. In many cases an identity column is used as a primary key, however this is not always the case.

SQL server uses the identity column as the key value to refer to a particular row. So only a single identity column can be created. Also if no identity columns are explicitly stated, Sql server internally stores a separate column which contains key value for each row. As stated if you want more than one column to be having unique value, you can make use of UNIQUE keyword.

like image 44
stack programmer Avatar answered Sep 29 '22 02:09

stack programmer