Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
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
show 3 more comments

1 Answer

up vote 1 down vote

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.

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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