how to auto increment primary key in visual studio built in sql server?

Question

I am using Visual Studio 2010 premium, and I created a database by using Visual Studio database tool. I set a field as primary key and I want to make it auto incremented.

But when I look at the properties it is not active. I mean I cannot set using the Visual Studio 2010 premium built in database tool. I cannot set the identity specifications. Is it because I'm using Visual Studio 2010 premium? How can I make the productID column auto increment?

Answer 1

Change it's data type to int (or similar). You can't set an IDENTITY specification on a nchar(10) column.

From CREATE TABLE:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

(My emphasis)

Answer 2

To make a primary key column auto increment, you need to create it as an int with IDENTITY eg:

[Id] INT NOT NULL PRIMARY KEY IDENTITY,  

Or via Visual Studio (2017):

• Right-Click the column > Properties > Identity Specification > Is Identity > True