CODEWITHSUNDEEP
sql-serverclustered-index
Kasum
Kasum

Reputation: 41

Multi-column Primary Key vs Auto-Increment

I made two columns to be Primary.

One Column is an INT, the other is an nvarchar(200).

Now something like this is actually allowed:

Int  Varchar
1    FIRSTENTRY
1    SECONDENTRY
2    FIRSTENTRY
2    SECONDENTRY

I made the INT Column an Identity which increments by 1.

The Problem is: When I INSERT new Columns it counts always up. Lets say I INSERTED the columns like this:

FIRSTENTRY
SECONDENTRY
FIRSTENTRY
SECONDENTRY

The Table looks then like this:

1 - FIRSTENTRY
2 - SECONDENTRY
3 - FIRSTENTRY
4 - SECONDENTRY

I want it to be like this:

1 - FIRSTENTRY
1 - SECONDENTRY
2 - FIRSTENTRY
2 - SECONDENTRY

Is there a standard way to do this?

Upvotes: 1

Views: 405

Answers (1)

Peter B
Peter B

Reputation: 24137

You can't: IDENTITY always increments, being part of a composite key plays no role.

Or in other words, there is no such thing as 'do the auto-increment only if it is needed to avoid a duplicate composite key'.

You will need to generate the recurring/repeating values yourself using custom written logic.

Update: You might also want to consider separating the two: an EntryGroup table that has a IDENTITY Primary Key, and an Entry table that has a column EntryGroupID that is a Foreign Key to the EntryGroup table. Sooner or later you may find that the EntityGroups are actually relevant entities in your system, and then you'll be glad if you separated them at such an early stage.

Upvotes: 1

Related Questions