Auto-increment Problem

[Pr0ph3ts]

Hey,

Here is what happened:

Users for a long time have been able to post new topics in our forums. However, a short time ago, the some users began to experience problems. What I have narrowed it down to is that upon inserting into the table, sometimes id value for the topic is the same as an id that is already in the table, so it fails to insert the record (due to a constraint). However, the topic id column is an auto-increment column and should just assign the next number for the id value.

Any ideas?

 

[gmmastros]

SQL Server does not have auto-increment, but Access does. SQL Server has identity columns.

What type of database are you using? This forum is specifically for SQL Server questions. If you are using an Access database, then you are likely to get an acceptable answer if you post your question in one of the Access forums.

try here: forum701



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Pr0ph3ts]

I am using SQL Server and you are correct it is set as an identity column.

 

[gmmastros]

Well... in that case... you've come to the right place.

Can you show the code you use to insert data?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Pr0ph3ts]

To insert the topic info into the table we call the following the message_posttopic stored procedure, which is defined below:

CREATE PROCEDURE message_posttopic @sectionID INT,@userID INT,@addusertopic BIT,@subject VARCHAR(100),@body TEXT,@topicID INT OUTPUT AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO message_topic(sectionID,userID,subject) VALUES(@sectionID,@userID,@subject)
IF (@@ERROR <> 0) GOTO CATCH
SELECT @topicID = @@IDENTITY
IF (@@ERROR <> 0) GOTO CATCH
INSERT INTO message_msg(topicID,userID,body) VALUES(@topicID,@userID,@body)
IF (@@ERROR <> 0) GOTO CATCH
IF @addusertopic = 1 BEGIN EXECUTE message_addusertopic @userID,@topicID END
IF (@@ERROR <> 0) GOTO CATCH
EXECUTE user_setstatus @userID
IF (@@ERROR <> 0) GOTO CATCH
COMMIT TRANSACTION
GOTO FINALLY
CATCH:
ROLLBACK TRANSACTION
FINALLY:

 

[gmmastros]

I'm gonna go out on a limb here...

I'm going to guess that you recently added a trigger to the message_topic table. Furthermore, I'm going to guess that this trigger inserts rows in to another table that has an identity column.

This is exactly the reason why you should NEVER use @@identity. You should use Scope_Identity() instead.


SELECT @topicID = [!]Scope_Identity()[/!]

Now would be a good time to review all of your code to see if there are any other procedures that are using @@identity. Best to change them all right now before this problem happens with another procedure.

Run this...

Select Routine_Name 
From   Information_Schema.Routines 
Where  Routine_Definition Like '%@@identity%'

Everywhere you use @@identity has this same potential problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Pr0ph3ts]

I have not added any trigger's recently, but i did replace @@identity, but I am still having the issue.


When I try to execute the command I get the following exception:

Violation of UNIQUE KEY constraint 'IX_message_topic'. Cannot insert duplicate key in object 'dbo.message_topic'

 

[SQLSister]

That isn't necessarily the identity column. YOu may have a unique constraint on the natural key and are trying to inseret the same record twice. look at that particular index to see what the issue really is.

"NOTHING is more important in a database than integrity." ESquared

 

[Pr0ph3ts]

I took a look and the constraint is on sectionID(ASC), lastPostID(DESC). But that shouldn't be creating the problem because there is never a time when a post id isn't unique.

 

[gmmastros]

Could it be that you have bad data in your tables now (as a result of using @@identity in other procedures)? Specifically, I am suggesting that you check to see if there are any lastPostId's in the table that shouldn't be there.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I would also suggest that you investigate the data in the table for one of the records that failed to insert. I think you will see the problem then. I'm willing to bet there are times when the lastpostid is not unique.

"NOTHING is more important in a database than integrity." ESquared