Post reply

Violation of Primary key

  • September 27, 2016 at 10:00 pm

    #400785

    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

  • September 28, 2016 at 2:34 am

    #1903566

    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]

  • September 28, 2016 at 2:41 am

    #1903568

    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

  • September 28, 2016 at 3:37 am

    #1903573

    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.

  • September 28, 2016 at 4:02 am

    #1903576

    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

  • September 28, 2016 at 5:16 am

    #1903593

    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