29

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?

enter image description here

modest and cute girl
  • 785
  • 4
  • 13
  • 24

2 Answers2

28

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)

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • ok one more question? can you write a simple sample insert statement since productID will be incremented automatically will i just insert productName??? – modest and cute girl May 08 '13 at 07:19
  • I'm not sure what you're asking for there. – Damien_The_Unbeliever May 08 '13 at 07:20
  • 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? – modest and cute girl May 08 '13 at 07:37
  • 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](http://msdn.microsoft.com/en-us/library/ms177564.aspx) or [`SCOPE_IDENTITY`](http://msdn.microsoft.com/en-GB/library/ms190315.aspx) – Damien_The_Unbeliever May 08 '13 at 07:42
5

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

enter image description here

Andrew
  • 18,680
  • 13
  • 103
  • 118