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. :)

Grantham

We can't help you unless you show us the create statement for the table in question. – Divorcee

You have three inserts. Can you tell which one is failing? – Henceforth

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 – Dermatoid

I created the table manually. the information i can give is: categorieID is an AUTO_INCREMENT and partenerID is the primary key – Grantham

@Henceforth The insert that is failing is: Query("INSERT INTO prod_categorie (categorieID, parentID) VALUES (?, ?)", array($chkParent, $txtParentCategorie)); – Grantham

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. – Divorcee

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 – Dermatoid

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. – Henceforth

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. – Acetabulum

@Henceforth 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, :) – Grantham

@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. – Henceforth

Answer 1

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.

Henceforth

Answer 2

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

Brainpan

Yes. The problem happens when auto-increment is disabled on the primary-key. – Tertias

Answer 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.

Ec

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! – Woolf

Answer 4

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.

Mastic

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. – Ambulacrum

Answer 5

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);
    }
}

Acrospire

Answer 6

   public function collection(Collection $rows)
   {
        foreach ($rows as $row) 
        {
            $id = $row[0];
            $mail = $row[0];
            if($id){
                DB::table('mdl_user')->where('email','=',$mail)->update(['firstname'=>$id]);
                return response()->json("data updated", 200);
            }
            else{
                return response()->json("No data updated", 401);
            }
        }
   }

Encroachment

"Any answer that gets the asker going in the right direction is helpful, but do try to mention any limitations, assumptions or simplifications in your answer. Brevity is acceptable, but fuller explanations are better." - check How do I write a good answer. Please provide some explanations on how this answer's the OP's question. – Germano

Answer 7

Just tick the checkbox A_I in the ID fields section.

Good luck.

Credential

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Polack