Violation of primary key constraints, Cannot insert a duplicate key - In new empty tables

Question

I know this is a common issue and I've read about it here and elsewhere but in most cases the problem is that a row already exists in the database (which I'm checking for...). In my case it's a new db with empty tables (well.. except for Publishers table that has one row in Id = 0), the code goes like this (Entity Framework V5):

public void InsertPublisherResult(PublisherResult pResult)
{
    using (mydbEntities e = new mydbEntities())
    {
        if (e.Publishers.Any(c => c.Name == pResult._Publisher.Name))
            continue;

        e.Publishers.Add(scraperResult._Publisher);

        foreach (RelatedPublisher rp in pResult._RelatedPublishers)
        {
            e.RelatedPublishers.Add(rp);
        }
        foreach (Download d in pResult._Downloads)
        {
            e.Downloads.Add(d);
        }
        foreach (PublisherDomain pd in pResult._PublisherDomains)
        {
            e.PublisherDomains.Add(pd);
        }
        e.SaveChanges();
    }
}

The rest of the tables (RelatedPublishers, Downloads and PublisherDomains) are empty, because they have non of the mentioned objects in the first pResult, with Primary Key - Id which is the defined entity key.

The first pResult is skipped because it exists, and the second one throws the exception on the PK violation.

Could it be that I'm trying to insert the 2nd pResult in the first row (id=0 on Publishers)? and if not, then what am I doing wrong?

The comment that was removed was helpful... apparently it's not auto incremented and now I'm trying to solve this. It can't be changed using the ssms so I'm looking for the `ALTER` syntax to change `Id` to be `ID` and `auto_increment`. — Moshisho, Feb 20 '14 at 08:17

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Thanks to the commenter that asked me if Id is auto incremented I checked it and got to the answer:

The column wasn't auto incremented and I had to drop the column or in my case I dropped the table and created it again using the correct SQL statement:

CREATE TABLE [mydb].[dbo].[Publishers]
(
Id  Integer IDENTITY(1,1) primary key,
PublisherGuid uniqueidentifier,
Name nvarchar(100),
Link nvarchar(100)
);

After that I Updated the Model from Database and it worked! That comment saved me lots of time...

Violation of primary key constraints, Cannot insert a duplicate key - In new empty tables

1 Answers1