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.
| Author |
Topic |
|
samisimon
Starting Member
1 Post |
 Posted - 2012-09-17 : 07:51:01
|
| Dear,I got a strange behavior from SQL2000 2 days ago. At some point, a table return the following message when trying to insert a record: Violation of PRIMARY KEY constraint '***'. Cannot insert duplicate key in object '***'. The table has 'Identity Specification' set to true, and this how it has been since we first created the DB. After some investigations we find out that the table's Identity is less then what it is supposed to be (supposed to be 311080 but "DBCC CHECKIDENT ('***')" returns 311054) and we already have a record with id=311054. We fixed the identity using: DBCC CHECKIDENT ( '***',RESEED, 311090) and everything is back to normal. My question is: what could have caused such a behavior? What wrong could have happened so that the table's IDENTITY is less then what it should return?thank you so muchSami Simon |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-17 : 08:20:01
|
| One possibility is that just like you fixed the identity using reseed, someone might have changed the identity seed to a lower number. Another possibility is that someone could have manually entered a higher value than the next identity number. They could have done that after setting the set identity_insert on option: http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 10:45:29
|
| we had one issue like this and eventually it turned out that reason was latter. someone entered a higher identity value for indicating a special data in table by setting identity insert on------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Table Identity Problems