0

can a unique column in SQL be auto_increment,... i.e. if we have a database table, which has a Primary Key Column "id" with identity (1,1). Then, can we have any other column with UNIQUE constraint and IDENTITY(1,1) with it.?

mohd
  • 11
  • 2
  • Few questions, why would you need two columns of the same data ?, any specific reason – DataWrangler May 05 '17 at 09:13
  • This seems to similar to [Identity Column StackOverflow](http://stackoverflow.com/questions/349092/can-a-sql-server-table-have-two-identity-columns) – DataWrangler May 05 '17 at 09:38
  • ok lets change the data, IDENTITY(500,1)... :) , but anyway, the answers below clarifies that, we can't have two columns having "IDENTITY" – mohd May 05 '17 at 11:57

2 Answers2

1

You can only have one identity column per table, but you can have as many unique constraints (or indexes) as you want.
In fact, if you are using an int identity column as your primary key, it's highly recommended to have at least one more unique index on your table.
The reason for this is that using a surrogate primary key without enforcing uniqueness on the natural key means your database can't enforce data integrity correctly. (Bonus reading - Natural vs. Surrogate Keys in SQL Server : Getting the Proper Perspective)

BTW, the fact that the column is an identity column does not mean it's unique. It will only be unique as long as no one mess with it - identity columns can be inserted explicit values using set identity_insert on, and can be re-seeded using DBCC CHECKIDENT sql command.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • [Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled May 05 '17 at 12:09
0

The answer is no.

Only one identity column can be created per table.

Read reference here: https://learn.microsoft.com/

TriV
  • 5,118
  • 2
  • 10
  • 18