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 18 · 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 11 · Answer 2 · edited Aug 02 '17 at 06:54

11

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

edited Aug 02 '17 at 06:54

Moradnejad

3,466
2
30
52

answered Jul 23 '15 at 04:20

Dung

19,199
9
59
54

Yes. The problem happens when auto-increment is disabled on the primary-key. – Jul 21 '21 at 19:50

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

4

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

26,085
12
82
103

answered Mar 16 '14 at 17:04

Yozaira R.

51
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

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

score 0 · Answer 6 · edited May 26 '22 at 20:53

0

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

edited May 26 '22 at 20:53

Richard

2,226
2
19
35

answered May 19 '22 at 23:14

seralkhatem musa

1

*"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](https://stackoverflow.com/help/how-to-answer). Please provide some explanations on how this answer's the OP's question. – Kuro Neko May 20 '22 at 01:23

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

6 Answers6

Linked