-3

When i try to insert into the table with this pk (auto incremental)

[foo] [bigint] IDENTITY(1,1) NOT NULL,

I get the following error message:

Msg 2627, Level 14, State 1, Procedure foobar, Line 39 [Batch Start Line 2]
Violation of PRIMARY KEY constraint 'PK_bar'. Cannot insert duplicate key in object 'table_foo'. The duplicate key value is (9).
The statement has been terminated.

(1 row affected)
  • we do not try to write into the identity column
  • There are around 4mio records in the table, so max pk value is not the issue. Another procedure which writes into this table works fine.
VJ87
  • 132
  • 2
  • 15
  • 6
    You did not include the insert statement in your question. Please provide an [mcve], see also [ask] – Igor Jul 19 '18 at 13:38
  • Please tell us what you would want to know if you were solving this problem. – nicomp Jul 19 '18 at 13:38
  • Check on what column this key is created, check if the table you are inserting the value of the column on which the key is = 9, check if you are not trying to insert data that try to insert 9 into this column – Killer Queen Jul 19 '18 at 13:39
  • Please explain to my how the complete insert statement will help you with this problem, when i specifically stated that we dont insert into the identity column. – VJ87 Jul 19 '18 at 13:39
  • pk is auto incremental – VJ87 Jul 19 '18 at 13:40
  • It's possible that someone has been messing with things they shouldn't have: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017 – MatBailie Jul 19 '18 at 13:42
  • 4
    This can happen if your identity got reseeded. Check the output of [`DBCC CHECKIDENT('table', NORESEED)`](https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql). – Jeroen Mostert Jul 19 '18 at 13:43
  • That is not possible – VJ87 Jul 19 '18 at 13:43
  • Is foo the PK? From your statement, it seems so, though isn't obvious. – Eli Jul 19 '18 at 13:44
  • Checking identity information: current identity value '9', current column value '4182913'. How can this happen and how to fix this? Thank you @JeroenMostert – VJ87 Jul 19 '18 at 13:45
  • 2
    It can happen exactly when someone issued a `DBCC CHECKIDENT('table', RESEED, )` command when they shouldn't have (or some process did it for them). The fix is simply `DBCC CHECKIDENT('table', RESEED)` with no value. – Jeroen Mostert Jul 19 '18 at 13:46
  • You're 100% confident that all of these are true? ***1.*** The PRIMARY KEY is on `table_foo(foo)`? *(You show the identity column, nothing that evidences that column is also the primary key)* ***2.*** That you are using `INSERT INTO table_foo (col_x, col_y)`, specifying all the relevant columns, and expressly not specifying column `foo`? ***3.*** That no-one has reseeded the identity? ***4.*** That the query you're looking at is really responsible for the `INSERT`? *(It's not from a trigger, etc?)* – MatBailie Jul 19 '18 at 13:47
  • Possible duplicate of https://stackoverflow.com/questions/21824478/ – Igor Jul 19 '18 at 13:48
  • Please provide the full table structure, the full insert statement (example) and the maximum value which is currently stored in your identity column. – Tyron78 Jul 19 '18 at 13:49
  • Possible duplicate of https://stackoverflow.com/questions/510121/ – Igor Jul 19 '18 at 13:49
  • I said " the table with this pk " and then i post the pk right under it. – VJ87 Jul 19 '18 at 13:50
  • Problem is with reseeding of the table ... if you are lucky and IDs of `1 to 9` never got deleted from table you are fine, just reseed it back to `4182913` and be happy – Veljko89 Jul 19 '18 at 13:51
  • And the DDL didn't include the `PRIMARY KEY` key words. So it ***is*** possible you were mistaken and just assumed that the `IDENTITY` column was also the PK. People make all sorts of mistakes, asking for clarification on this one helps eliminate them. – MatBailie Jul 19 '18 at 13:51

1 Answers1

1
DBCC CHECKIDENT('table', NORESEED)

returned the mismatching values.

DBCC CHECKIDENT('table', RESEED)

Fixed it. Thanks @Jeroen Mostert

VJ87
  • 132
  • 2
  • 15
  • Note that "another procedure which writes into this table works fine" is fishy, because this suggests that procedure does use `identity_insert`, and there may be something else (like a scheduled job or other maintenance procedure) that issues `RESEED`s in an attempt to keep identities correct, but which is misfiring. You may want to try a `select [definition] from sys.sql_modules where definition like '%reseed%'`. – Jeroen Mostert Jul 19 '18 at 14:03
  • There wont be a reseeding and i either forgot about it or something else must have broken the identity. Either way its fixed and it was the first time it accured so i wont do anything other about it – VJ87 Jul 20 '18 at 14:50