primary key violation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

primary key violation

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2006-10-09 : 23:17:33

i got a table with the following:
LogID int

this column is not an identity column, i cannot change this because its being replicated, if i change the structure i need to setup replication again.

at the moment i do max(logid) + 1 to get the next id, but when we call this in quick succession it fails saying PK violation, any ideas how i can fix this without changing the table structure?

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-10 : 00:58:31

You are probably getting error because two users getting same value for primary key...you should enclose the LogId increment part inside the transaction like this:

Begin Transaction
Select @LogId = max(LogID) + 1 from Table
Update Table set LogId = LogId + 1
Commit Transaction

It would be better if you keep this part as a separate SP returning newly generated key value.

Harsh Athalye
India.
"Nothing is Impossible"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-10 : 10:25:17

An identity column would be a better solution.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources