I am user the following as primary key in the SqlDB: I am doing the Index as Autoincrement, but it is autoincremented in general not per user. Example userid=1,index=1, then insert another for userid=2 then inserted userid=2index=2 Then we insert a new entry for userid=2 theninserted userid=2 index=3 and not 1.

Is it possible to do what I want?

  • 1
    Yoav - It is not clear what you want to do. What do you want to happen? – JHFB Mar 27 '12 at 19:29
  • What RDBMS and version? – JNK Mar 27 '12 at 19:35
  • Hey - RDBMS - what is it? I want to have a primary key userId,Index - Index autoincremented - should be 0..N for each userId – Yoav Mar 27 '12 at 20:01
  • I think I did not put the index as the PK..... Maybe that is the cause. missed it – Yoav Mar 27 '12 at 20:02
  • 1
    Then unless you are using SQL Server 2012 there's no "good" way to do this. – JNK Mar 27 '12 at 20:09

You can't have more than one IDENTITY column per a table. Use MAX(index)+1 where userid=X, when inserting a new row without auto-increment.

| improve this answer | |
  • I guess there is a function needs to be created in the SQL Table. Unfortunatly I have no idea how to do this. Am I right? Can you explain a bit or send a link ? – Yoav Mar 28 '12 at 11:21
  • 1
    The first part is true; the second part is fraught with peril unless the race condition is eliminated through the use of locking hints or error handling. – Jon Seigel May 27 '12 at 21:56
  • The OP doesn't want more than one IDENTITY columns. My interpretation from the confusing description is that he needs a column (identityID) which is not Unique but the combination (userID,identityID) to be Unique. – ypercubeᵀᴹ Jun 26 '12 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.