Databases
StackOverflow2013
Postsdbo
POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
    primarykey
    data
    text
    <p>When I want to persist a complex model, I get this error. I think I know where it comes from, but I don't know how to solve it. I'm importing a few feeds and create objects automatically, including children (many-to-many).</p> <blockquote> <p>{"Violation of PRIMARY KEY constraint 'PK_dbo.Parent'. Cannot insert duplicate key in object 'dbo.Parent'. The duplicate key value is (291).\r\nThe statement has been terminated."}</p> </blockquote> <p>The error speaks for itself, but how to prevent it? :)</p> <p><strong>The code that triggers it</strong></p> <pre><code>var parser = new SchoolFeedReader(); var update = parser.GetAll(); var students = Mapper.Map&lt;List&lt;StudentDTO&gt;, List&lt;Student&gt;&gt;(update); using (var db = new SchoolContext()) { // I'm updating every night, so clean out the database before import db.Database.ExecuteSqlCommand("DELETE FROM Student"); db.Database.ExecuteSqlCommand("DELETE FROM Parent"); db.Database.ExecuteSqlCommand("DELETE FROM Subject"); db.Database.ExecuteSqlCommand("DELETE FROM StudentParent"); db.Database.ExecuteSqlCommand("DELETE FROM StudentSubject"); students.ForEach(s =&gt; db.Students.Add(s)); db.SaveChanges(); // Triggers the Exception } </code></pre> <p><strong>The TL;DR</strong></p> <p>For a schoolproject I need to import 3 XML Feeds into the database.</p> <ul> <li>Students.xml</li> <li>Parents.xml</li> <li>Subjects.xml</li> </ul> <p>In Students.xml I encountered a design flaw: a fixed number (3) of possible Parents.</p> <pre><code>&lt;student&gt; &lt;StudentId&gt;100&lt;/StudentId&gt; &lt;Name&gt;John Doe&lt;/Name&gt; &lt;Location&gt;Main Street&lt;/Location&gt; &lt;Parent1&gt;1002&lt;/Parent1&gt; &lt;Parent2&gt;1002&lt;/Parent2&gt; &lt;Parent3/&gt; &lt;/student&gt; (... more students) </code></pre> <p>In Parents.xml, things are more straightforward.</p> <pre><code>&lt;parent&gt; &lt;ParentId&gt;1102&lt;/ParentId&gt; &lt;Name&gt;Dad Doe&lt;/Name&gt; &lt;Email&gt;dad@doe.com&lt;/Email&gt; &lt;/parent&gt; (... more parents) </code></pre> <p>And Subjects.xml is also very simple.</p> <pre><code>&lt;subject&gt; &lt;StudentId&gt;100&lt;/StudentId&gt; &lt;Name&gt;English&lt;/Name&gt; &lt;/subject&gt; (... more subjects) </code></pre> <p><strong>The Models</strong></p> <p>So I created 3 models, including the DTOs.</p> <pre><code>public class Student { [DatabaseGenerated(DatabaseGeneratedOption.None)] public long StudentId { get; set; } public string Name { get; set; } public string Location { get; set; } [InverseProperty("Students")] public virtual ICollection&lt;Parent&gt; Parents { get; set; } public virtual ICollection&lt;Subject&gt; Subjects { get; set; } } public class StudentDTO { public long StudentId { get; set; } public string Name { get; set; } public string Location { get; set; } public List&lt;ParentDTO&gt; Parents { get; set; } public List&lt;SubjectDTO&gt; Subjects { get; set; } } public class Parent { [DatabaseGenerated(DatabaseGeneratedOption.None)] public long ParentId { get; set; } public string Name { get; set; } public string Email { get; set; } [InverseProperty("Parents")] public virtual ICollection&lt;Student&gt; Students { get; set; } } public class ParentDTO { public long ParentId { get; set; } public string Name { get; set; } public string Email { get; set; } public List&lt;StudentDTO&gt; Students { get; set; } public ParentDTO() { Students = new List&lt;StudentDTO&gt;(); } } public class Subject { public long SubjectId { get; set; } public string Name { get; set; } public virtual List&lt;Student&gt; Students { get; set; } } public class SubjectDTO { public string Name { get; set; } public List&lt;StudentDTO&gt; Students { get; set; } public SubjectDTO() { Students = new List&lt;StudentDTO&gt;(); } } </code></pre> <p><strong>From XML to DTOs</strong></p> <p>The Importer class has this giant LINQ query to get everything I need in one big swoop.</p> <pre><code>var query = from student in _xStudents.Descendants("Student") select new StudentDTO { StudentId = (long)student.Element("StudentId"), Name = (String)student.Element("Name"), Subjects = ( from subject in _xSubjects.Descendants("Subject").DefaultIfEmpty() where (String)student.Element("StudentId") == (String)subject.Element("StudentId") select new SubjectDTO { Name = (String)subject.Element("Name") } ).ToList(), Parents = ( from parent in _xParents.Descendants("Parent").DefaultIfEmpty() group parent by (String)parent.Element("ParentId") into pg where (String)student.Element("Parent1") == (String)pg.FirstOrDefault().Element("ParentId") || (String)student.Element("Parent2") == (String)pg.FirstOrDefault().Element("ParentId") || (String)student.Element("Parent3") == (String)pg.FirstOrDefault().Element("ParentId") select new ParentDTO { ParentId = (long)pg.FirstOrDefault().Element("ParentId"), Name = (String)pg.FirstOrDefault().Element("Name") } ).ToList() }; </code></pre> <p>That works fine, some students get 2 parents, some get 1, so my data looks good.</p> <p><strong>The Problem</strong></p> <p>I have these AutoMappers in my Global.asax.cs:</p> <pre><code>Mapper.CreateMap&lt;StudentDTO, Student&gt;() .ForMember(dto =&gt; dto.Parents, opt =&gt; opt.MapFrom(x =&gt; x.Parents)) .ForMember(dto =&gt; dto.Subjects, opt =&gt; opt.MapFrom(x =&gt; x.Subjects)); Mapper.CreateMap&lt;ParentDTO, Parent&gt;(); Mapper.CreateMap&lt;SubjectDTO, Subject&gt;(); </code></pre> <p>But when I start the import I get errors on my <code>db.SaveChanges()</code>. It complains about a duplicate ForeignKey on the Parent model. So I'm thinking: </p> <blockquote> <p><em>it's a Many-to-Many relationship, so if John Doe's sister, Jane Doe, tries to insert the same Dad Doe, then it crashes</em></p> </blockquote> <p>So How can I make sure that the entire set of Mapped Business Objects only have 1 reference to each entity; how to <strong>delete the duplicate daddy's and mommy's</strong>? I probably want to do this also for Subject.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNaNerd
    1. USNaNerd
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOn a related note: if you want to have two parents, then add foreign keys for Parent1 and Parent2 (Mother and Father in historical terms). Creating many to many relation for parenthood allows someone to have 20+ parents, like Eric Cartman.
      singulars
      1. POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
      1. USZoran Horvat
    2. COOn a second thought: Since one parent may have multiple children among students, if you try to insert the same parent twice you'll get the primary key constraint violation.
      singulars
      1. POViolation of PRIMARY KEY constraint: Cannot insert duplicate key in object
      1. USZoran Horvat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload