Set Maximum Value for an Identity Column in SQL SERVER 2005

Question

Hi all,

I am developing an Windows pplication(using Visual Studio 2008,Sql server 2005). I have to maintain userlog information. I am having an identity column. It is working fine. But now the value of identity column is 172. Again when I try to log on I am getting the error as:

Violation of Primarykey pk_userLogId cannot insert duplicate values in an object'dbo.UserLog'. The statement has been terminated.

When I close the error dialog box I am able to still use the application but the userlog is not being maintained.

What should I do to avoid getting this error message?

Is there any way I could set the maximum value for an identity column?

please help me out! Thanks in advance!

Answer 1

Have you set your identity columns to autoincrement. It doesn't sound like a numeric range problem. Rather, it looks like you're explicitly providing the primary key value when creating a new UserLog entry. The error message leads me to believe that a record already exists within table UserLog with the same primary key value that you're trying to add.

Answer 2

EDIT:

My initial response was wrong because it was based on a misunderstanding about the log table schema. In view of Sheetal's later posting of this schema (and my realizing that the name of the column was UserLogId, not UserLoginID...) this hypothesis is invalidated.

The log table is therefore,

CREATE TABLE USERLOG 
( UserLogId int identity (1,1) CONSTRAINT pk_UserLogId primary key, 
  UserName nvarchar(50) not null, 
  LogInTime datetime CONSTRAINT dft_LogInTime default getdate() 
) 
--ADDING LOGOUT TIME ALTER TABLE USERLOG ADD LogOutTime datetime

making the OP question the more puzzling...

How can one get PK violation conditions with an identity column? (unless the INDENTITY_INSERT setting for that table, and that manual edits/insert that include a value for the PK column, maybe Sheetal can shed some light on that... ? )

Sheetal should therefore look into the application's logic, in the area of the code that completes the login, after the user is authenticated, and look for instance of queries where a value is specified for the UserLogId column (other than in WHERE clauses, of course).

BTW, I'm not sure why we're all trying so hard? given Sheetal's own minimal effort with framing his questions, even when considering a possible handicap with English language, and with providing information... and certainly his acceptance rate... ;-)

-- the following is wrong (based on wrong hypothesis) ----

The issue seems to be that the UserLoginId is declared as the Primary Key for the log table. This is a odd choice for such a table because we'd expect to have many log records for a given user.

In a SQL table, the primary key constraint essentially instructs SQL to refuse any INSERT request for a records with a Primary Key value that readily exist in the table. (And to return an error condition similar to the one mentioned in the question)

It's hard to advise not knowing the log table schema and use case, but you can maybe remove the pk constraint altogether or use another column for it. An auto-incremented column called "EventId" could be a good choice for it. You'd get something like that in the log table:

EventID (PK)   DateTime              UserLoginId  EventType     Details
1              01/02/2008 06:15:01   172          LogIn         Stan
2              01/02/2008 06:15:21   321          LogIn         Jeff
3              01/02/2008 06:15:24   172          FileDownload  Report7.pdf
4              01/02/2008 06:15:54   172          FileDownload  SalesTraining.pdf
5              01/02/2008 06:17:21   321          LogOut

etc...