Auto-Increment with a two column primary key

Question

Auto-Increment with a two column primary key

kcook-1066767

Old Hand

Points: 367
More actions
November 5, 2009 at 7:44 pm

#98332

I have two columns in a table representing the primary key; I want the ability to auto-increment one of these columns relative to the other primary key. For example, lets call the columns PK1 and PK2, and add one more additional column called data, and let's assume the following data is present:
PK1 PK2 Data
0 0 AAA
0 1 BBBB
1 0 CCCCCC
1 1 DDDDDD
1 2 EEEEEEE
I want the ability to insert a new row, auto increment PK2, but relative to a given PK1. Let's say the case above, I want to insert a row for PK1 = 0, Data = FFF. If I make PK2 an Identity Column, the upon inserting it adds the following row:
PK1 PK2 Data
0 3 FFF
But I want it to auto-increment PK2 by 1, relative to the condition PK = 1:
PK1 PK2 Data
0 2 FFF
I can use a SELECT MAX(PK2) + 1 WHERE PK1 = ?, followed by an INSERT INTO. But this has the risk of deadlocks in a multi-threaded, high volume environment.
Any thoughts?
Thanks,
Kevin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

why so complex a logic for your PK? maybe I'm missing the reason because of the way you abstracted it to pseudocode.

why won't a plain old tried and true identity work for the "real" pk, but you use a view with row_number() over(partition by col1,col2 order by Identityfield) to get the data laid out the way you want?

I can't see the advantage to your design yet...can you explain the business logic behind it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kcook-1066767 Old Hand Points: 367 More actions · Answer 2

Here is the create table statement ... I probably used the wrong terminology above; I want a clustered primary key:

CREATE TABLE [dbo].[Test](

[PK1] [int] NOT NULL,

[PK2] [int] NOT NULL,

[Data] [varchar](50) NULL,

CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED

(

[PK1] ASC,

[PK1] ASC

)

Basically, I need a unique PK2 for every PK2, but I don't want PK2 to be globally unique in the whole table (i.e. the reason from the clustered PK). Therefore, when I insert a new row of data, I want to provide it the PK1, and have the database auto-increment PK2 based on the last row for the given PK1.