Two column Primary Key with identity seed???

Question

Two column Primary Key with identity seed???

RustyT

Right there with Babe

Points: 758
More actions
January 21, 2009 at 2:39 pm

#204403

Hi,
I am in the process of designing a table that will have 2 columns make up the primary key/unique key. For example
Table 1:
SessionID int pk
PlanID int pk
Plandesc varchar
For the session id I will always know this value when inserting but for the PlanID i want it to increment from one, or if there is already a sessionid in the table then increment to the max value + 1 for planid. So the thing is I want to have the planid increment based on the sessionid. So for example:
Table1:
SessionID PlanID Plandesc
1111 1 Test1
1111 2 Test2
2222 1 Test3
2222 2 Test4
3333 1 Test5
I thought about checking the max value in a stored procedure and then inserting the correct planid but I was worried that if two people do this at the same time I could possibly get an incorrect planid and it would cause an error on the insert.
So... Is it possible to set something up in the design of the table that would handle this or is this something that I would have to handle in a stored procedure or possibly a trigger. Keep in mind that sessionid and planid id will be used to make the record unique.
thanks for any help
rusty

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

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

Eric SSCommitted Points: 1803 More actions · Answer 1

You might be better off using an identity column for PlanID. While the order would not be sequential for each SessionID it would be increasing and it would make the row unique.

You would still know the insert order and if you needed to display sequential numbering to a user you could do that with ROW_NUMBER.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

You can do it inside a proc . take a look at this..

create table #t (col1 int,col2 int)

insert into #t values (1111,2)

declare @r int

declare @t int

set @r = 1111

set @t = 1

insert into #t

select @r AS 'R', case when exists (select 1 from #t where col1 = @r)

then max(col2)+ 1 ELSE @t END

from #t where col1 = @r

select * From #t

Since the checking (exists clause) is along with the insert statement there is a very small chance that concurrent users might insert incorrect data.

There was a QOTD (question of the day) some time back which was very similar and generated a lot of discussion on this forum. See if you can find that QOTD.

"Keep Trying"

RustyT Right there with Babe Points: 758 More actions · Answer 3

Thanks for your responses. I will look into both ways. I will also try to hunt down the QOTD.

thanks

rusty