Violation of PRIMARY KEY constraint with Identity(1,1)

Question

I have been struggling with a non-consistent issue for a while, and am hoping to get this figured out. At random times my system will have a group of 4-5 INSERTS that fail due to:

Violation of PRIMARY KEY constraint 'PK_Quality_Checks'. Cannot insert duplicate key in object 'dbo.Quality_Checks'. The duplicate key value is (25943).

Most other questions I have read for this error involve using something other than an Identity(1,1) as the PK, or very specialized situations, so I am posting my own issue.

I am using Entity Framework to work with my SQL Server 2012 database. I parse an XML file to a object called Test_Run then convert that object to my Entity objects to add to the table.

All tables use an Identity(1,1) as their primary key.

My conversion code:

public static int SaveTestRun(TestRun testRun)
{
        gReportEntities gReportEntity = new gReportEntities();
        var testRunTable = new Test_Runs()
        {
            TRX_Name = testRun.TrxFile,
            Start_Time = testRun.StartTime,
            Enviroment = testRun.Environment,
            Mach_Config = testRun.MachineConfiguration,
            Product = testRun.Product,
            Details = testRun.Details,
            Duration = testRun.Duration.ToString(),
            End_Time = testRun.EndTime,
            Failed_Num = testRun.FailedTestCount,
            Passed_Num = testRun.PassedTestCount,
            Incom_Num = testRun.IncompleteTestCount,
            Abort_Num = testRun.AbortedTestCount,
            NotEx_Num = testRun.NotExecutedTestCount,
            Timed_Num = testRun.TimedOutTestCount,
            QC_Failed_Num = testRun.FailedQualityChecksCount,
            QC_Passed_Num = testRun.PassedQualityChecksCount,
            QC_Incon_Num = testRun.InconclusiveQualityChecksCount,
            Video_Dir = testRun.VideoRootPath,
            Custom_Html = testRun.CustomHtml

        };

        foreach (var test in testRun.Tests)
        {
            var testResult = new Test_Results()
            {
                Test_Class = test.Value.TestClass,
                Test_Name = test.Value.Name,
                Result = test.Value.Outcome.ToString(),
                Start_Time = test.Value.StartDate,
                Guid = test.Value.Id,
                Duration = test.Value.Duration.ToString(),
                Category = String.Join(", ", test.Value.Categories),
                Error_Message = test.Value.ErrMessage,
                Stack_Trace = test.Value.StackTrace,
                Video_Link = test.Value.VideoPath,
                Comment = test.Value.Comment
            };

            foreach (var qCheck in test.Value.QualityChecks)
            {
                var qualityCheck = new Quality_Checks()
                {
                    Check_Name = qCheck.Name,
                    Check_Outcome = qCheck.Outcome.ToString(),
                    Message = qCheck.Message
                };
                testResult.Quality_Checks.Add(qualityCheck);
            }

            testRunTable.Test_Results.Add(testResult);
        }

        gReportEntity.Test_Runs.AddObject(testRunTable);

        gReportEntity.SaveChanges();

        return testRunTable.Test_Run_Id;
}

I need to get this issue fixed or my tool cannot be fully implemented.

Answer 1

Typically if an identity column is violating a key constraint, it's because something got added to your table with an ID higher than the value that it's currently seeded at. So when you go to insert a new value, it finds that there's already an entry with that ID.

You'll probably want to reseed your identity column.