How do you create an compound primary key where the 2 column in the key is identity?

[emaduddeen]

Hi Everyone,

I have a table that has a 2 column primary key. I set up the key where the 2nd column is an identity column.

When I ran my VB application I discovered the ID (2nd column) did not increment the correct way. I was hoping it would increment like this:

Category            ID

50001                1

50001                2

50001                3

50002                1

50002                2

Instead it did this:

Category            ID

50001                1

50001                2

50001                3

50002                4

50002                5

I'm using Visual Studio 2010 to do this.

Can you tell me what I did wrong? I used to use Clarion and it worked when telling it that the 2nd column was an auto incrementing column but I'm still new to SQL Server.

Thanks.

Truly,
Emad

[buddylee17]

Defining the column as Identity, means that each value is unique and will never repeat. It is an identifier for the row.

[emaduddeen]

Hi Everyone,

Since only 1 column can be an identity column I created a column in a table to hold the cateogy and another one to hold the id which I increment manually based on the category.

Truly,
Emad