Violation of Primary key

  • Hi All,

    Please I need help on how to solve this problem. I have table made one of the columns Primary key with Identity Specification. I have existing records in the table is 128503, while inserting new record in the table. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (64251).

    I queried to the check the Max identity "select IDENT_CURRENT( '[dbo].[Piagam_PiagamCount]' )", its shows 64251 but i have the records count 128503. Please help me why it happens and solution for this,

    Thanks in Advance.

    My table:

    CREATE TABLE [dbo].[Piagam_PiagamCount](

    [piagam_id] [int] IDENTITY(1,1) NOT NULL,

    [permohonan_id] [int] NULL,

    [noRujPermohonan] [varchar](50) NULL,

    [tarikhPermohonan] [date] NULL,

    [piagamPelanggan] [int] NULL,

    [hariRemaining] [int] NULL,

    [hariUtilised] [int] NULL,

    [hariExceeded] [int] NULL,

    [tarikhUpdate] [date] NULL,

    [stopCount] [varchar](10) NULL,

    [moduleCode] [varchar](20) NULL,

    CONSTRAINT [PK_Piagam_PiagamCount] PRIMARY KEY CLUSTERED

    (

    [piagam_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • Will be hard unless we can see the code that is resulting in the error.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • It looks as if someone has reseeded the identity at some point. Find out the maximum value of piagam_id, and reseed the identity again to that value plus one.

    John

  • Thanks John,

    I did the same reseeded the identity. Its working fine now. If know clear root cause of this problem. I can avoid in future since its in Prod.

  • Ansharah (9/28/2016)


    If know clear root cause of this problem. I can avoid in future since its in Prod.

    Like I said, someone reseeded the identity. Since you need sysadmin, db_owner or db_ddladmin to run DBCC CHECKIDENT, make sure you control membership of those roles. Also, consider setting up something (extended events, for example) that will capture and record DDL changes to your database. You can write that yourself or buy something in.

    John

  • Thank you John.:-)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply