Two column Primary Key with identity seed???

  • 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

  • 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.

  • 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"

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

    thanks

    rusty

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

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