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?

Vesta

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)

Natashianatassia

ok one more question? can you write a simple sample insert statement since productID will be incremented automatically will i just insert productName??? – Vesta

I'm not sure what you're asking for there. – Natashianatassia

like for example Insert into Product (productID, productName) values (????, textBox1.Text) the ???? part? will i have to give a value to the primary key which is incremented automatically? – Vesta

Just miss out that column in both the column list and the VALUES: INSERT INTO Product (ProductName) VALUES (textBox1.Text). If you need to know what value was assigned, you'll want to look into the OUTPUT clause or SCOPE_IDENTITY – Natashianatassia

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

Anglian