Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

Question

I have a databse problem where i get Integrity constraint violation: 1062. I tried some things on my own but it didtn work so now i am asking you guys to see if you people can help me out.

elseif($action == 'add') {
if($_POST['create'] == true) {
    $title = $_POST['txtTitle'];
    $txtParentCategorie = $_POST['txtParentCategorie'];
    $txtContent = $_POST['txtContent'];

    if($txtParentCategorie == "niks") {
        $txtParentCategorie = NULL;
        $chkParent = 1;
        $order_count = countQuery("SELECT categorieID FROM prod_categorie WHERE parentID=?",array(1));
        $order = $order_count + 1;
    } else {
        $chkParent = null;
        $order_count = countQuery("SELECT categorieID FROM prod_categorie WHERE parentID is not NULL");
        $order = $order_count + 1;
    }

    Query("INSERT INTO prod_categorie (categorieID, parentID) VALUES (?, ?)", array($chkParent, $txtParentCategorie));
    $inserted_id = getLastInsertId();
    Query("INSERT INTO tekst (tabel, kolom, item_id, tekst, taalID) VALUES(?, ?, ?, ?, ?)", array('prod_categorie', 'categoriename', $inserted_id, $title, $lang));
    Query("INSERT INTO tekst (tabel, kolom, item_id, tekst, taalID) VALUES(?, ?, ?, ?, ?)", array('prod_categorie', 'content', $inserted_id, $txtContent, $lang));
    $languages = selectQuery("SELECT taalID FROM taal WHERE taalID!=?",array($lang));
}

when i run this the first INSERT INTO doesnt fill in any data and giving this error: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' there already is a primary 1 key in there. but it is on auto increment. in the tekst tabel the item_id gets an 0 input.

Javascript:

    $('.btnAddCategorie').click(function(){
    if(busy != 1){
        busy = 1;
        var error = 0;
        var gallery = $('select[name="gallery_dropdown"]').val();
        if($('input[name="txtTitle"]').val() == ''){
            error = 1;
            alert('Het titel veld is nog leeg');
            $('input[name="txtTitle"]').focus();
        }
        if(error != 1){
            $('.content_load_icon').html('<img src="../../includes/images/layout/load_small.gif" />');
            var content = $('#cke_ckeditor').children().children().children()[3].contentWindow.document.childNodes[1].childNodes[1].innerHTML;
            $.ajax({
                url: '../../action/ac_productbeheer.php?a=add',
                type: 'POST',
                data: {txtTitle: $('input[name="txtTitle"]').val(), txtForm: $('select[name="txtForm"]').val(), customGalTitle: $('.txtCustomGalleryTitle').val(), gallery_dropdown: gallery, txtParentCategorie: $('select[name="txtParentCategorie"]').val(), txtContent: content, txtMeta: $('.txtMetaDesc').val(), create: true},
                success: function(data, textStatus, xhr) {
                    $('.content_load_icon').html('');
                    $('.txtContentConsole').html('Product succesvol opgeslagen!').show().delay(2000).fadeOut(200);
                    busy = 0;
                    saved = 1;
                    window.location = '../../modules/productbeheer/index.php';
                },
                error: function(xhr, textStatus, errorThrown) {
                    $('.content_load_icon').html('');
                    $('.txtContentConsole').html('Fout bij opslaan! Probeer het later nog een keer.').show().delay(2000).fadeOut(200);
                    busy = 0;
                }
            });
        } else {
            error = 0;
            busy = 0;
        }
    }
});

html:

<a  class="btnAddCategorie"><img name="btnOpslaan" src="/'.CMS_ROOT.'/includes/images/layout/opslaan.png" /></a><span  class="content_load_icon"></span><span  class="txtContentConsole"></span>

Hope someone can help me on here. already alot of thanks in advance. :)

We can't help you unless you show us the create statement for the table in question. — N.B., Oct 14 '13 at 11:33
Are you trying to insert a value into your primary key? If so - don't (in the tekst tabel the item_id gets an 0 input.) -> Also post your table definition — AgRizzo, Oct 14 '13 at 11:35
I created the table manually. the information i can give is: categorieID is an AUTO_INCREMENT and partenerID is the primary key — Evert van de Lustgraaf, Oct 14 '13 at 11:37
@asantaballa The insert that is failing is: Query("INSERT INTO prod_categorie (categorieID, parentID) VALUES (?, ?)", array($chkParent, $txtParentCategorie)); — Evert van de Lustgraaf, Oct 14 '13 at 11:39
Run the following commands: `SHOW CREATE TABLE prod_categorie;` and `SHOW CREATE TABLE tekst;`, using PHPMyAdmin or any other tool that you use, copy that and edit your question with the information you copied. That way we'll be able to see what's going on. — N.B., Oct 14 '13 at 11:39
You should not be inserting a value in your auto-increment field. Specifically, you should not be inserting a value in categorieID in prod_categorie if that is an auto-increment — AgRizzo, Oct 14 '13 at 11:42
Ok. Then if categorieID is the auto-increment, then i think should _not_ be in your insert statement at all. The system will create it for you even if not in the insert statement. — asantaballa, Oct 14 '13 at 11:43
Does the array return more than 1 row? In a bulk insert that must be separated by VALUES('',''),('',''), or the primary key wont be able to increment the values. — Mad Dog Tannen, Oct 14 '13 at 11:51
@asantaballa and AgRizzo Thanks that did it. really cant get to it why I didnt see it myself. Thanks alot I think i wouldnt have found it without you help, :) — Evert van de Lustgraaf, Oct 14 '13 at 11:53
@AgRizzo, you put in yours before mine. Consider adding as answer so Evert can accept? And Evert please make sure to accept once he does so question shows as complete. — asantaballa, Oct 14 '13 at 12:14

score 15 · Accepted Answer · answered Oct 14 '13 at 14:07

When inserting into a table with an auto increment field, the auto increment field itself should not be specified at all.

Query("INSERT INTO prod_categorie (categorieID, parentID) VALUES (?, ?)", array($chkParent, $txtParentCategorie));
                                   ^^^^^^^^^^^                    ^             ^^^^^^^^^^

Should be just

Query("INSERT INTO prod_categorie (parentID) VALUES (?)", array($txtParentCategorie));

Just added as answer from comment discussion to allow accept and finishing the question.

score 9 · Answer 2 · edited Aug 02 '17 at 06:54

in my case the error is:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

the solution is to Empty/Truncate all records of the table in question

The problem happens when auto-increment is disabled on the primary-key of that table or the data-type is wrong.

partially credited to https://magento.stackexchange.com/questions/56354/admin-error-sqlstate23000-integrity-constraint-violation-1062-duplicate-ent

score 3 · Answer 3 · edited Mar 16 '14 at 17:22

3

I had the same problem, and it was not the auto increment that was causing it. I changed the data type on my table ID from TINYINT(3) to INT(10). Try that. Maybe it'll help.

edited Mar 16 '14 at 17:22

Alex Shesterov

21,630
10
65
88

answered Mar 16 '14 at 17:04

Yozaira R.

41
2

Just to give some validation to this answer, this was my problem. My table had `auto_increment` set and I was not specifying the primary key, but I was still getting the error. Saw my field type was set to `tinyint` instead of `int`. This answer helped me, thanks! – ckpepper02 Mar 05 '15 at 01:47

score 0 · Answer 4 · answered Jul 06 '16 at 15:41

0

I came across this problem when using Magento 2 with the Google Experiment set to Yes. Simply shutting it off solved my page save issue. Bu ti'm still having a problem with adding catalog products it give me an error that the URL Key for the specified store already exists. and the image is not uploading even though i have correct folder permissions. Will post an update in case it helps anyone else.

answered Jul 06 '16 at 15:41

Joshua Berkowitz

11
1

Hi Joshua, Just thought I could add (if you haven't already checked) I was getting a very similar issue and it turned out that my `catalog_product_entity_int` table had reached the greatest value of the `int` type and therefore doing funny things when I tried to create a new simple product. I understand what I am working on is `Magento 1` but the principle is the same, maybe the issue is an underlying data type constraint. – Nathaniel Rogers Dec 12 '16 at 04:09

score 0 · Answer 5 · answered Mar 23 '21 at 12:01

I had the same problem when I was using Http:put and Http:patch. So the problem was in my algorithm.

I was trying to save a duplicated ID in hero table, take a look:

 public function updateHero(Request $request){
    
    $id =hero::find($request->id);

    if($id){
        $theHero=new hero;
         $theHero->id=$request->id;
        $theHero->name=$request->name;
        $theHero->save();

        return response()->json("data updated", 200);
    }
    else{
        return response()->json("No data updated", 401);
    }
}

So I removed $theHero->id=$request->id; in my code.

public function updateHero(Request $request){
    
    $id =hero::find($request->id);

    if($id){
        $theHero=new hero;
        $theHero->name=$request->name;
        $theHero->save();

        return response()->json("data updated", 200);
    }
    else{
        return response()->json("No data updated", 401);
    }
}

Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

5 Answers5

Linked