#1062 - Duplicate entry for key 'PRIMARY'

Question

So my MySQL database is behaving a little bit wierd. This is my table:

Name shares id  price   indvprc
cat   2     4   81      0
goog  4     4   20      20
fb    4     9   20      20

I'm getting this #1062 error when I try to insert into the table. So I looked into it further and realized that when I try to insert values into the table, in which the name and shares values are the same, it will return the #1062 error. For example, If i inserted:

fb    4      6     20   20

It would return an error. But if i changed the shares number to 6, it would run fine. Is it because of one of my columns that could be unique, or is it just something with mysql?

what is the primary key on your table? You cannot have duplicate values in a primary key field. A primary key can also consist of multiple fields so what fields are in your key? — Taryn, Jul 24 '12 at 20:06
Don't know for sure, but could your database have formed a relationship between `shares` and `id` ? take a look in phpMyAdmin and see what you can find.... — dykeag, Jul 24 '12 at 20:08
Most likely you primary key is composed by the columns `Name` and `shares`. That means that the combination of both columns must be unique (in your example, the combination `fb, 4` already exists) — Lamak, Jul 24 '12 at 20:10
well in my phpmyadmin I cannot click on the primary key for both name and shares. Does that mean something? — irosenb, Jul 24 '12 at 20:16

score 15 · Accepted Answer · answered Jul 24 '12 at 20:12

15

You need to remove shares as your PRIMARY KEY OR UNIQUE_KEY

answered Jul 24 '12 at 20:12

mlishn

1,665
14
19

What I did was i dropped both `shares` and `name` and readded them with index of index. – irosenb Jul 24 '12 at 21:05

score 8 · Answer 2 · answered Jul 24 '12 at 20:10

8

Use SHOW CREATE TABLE your-table-name to see what column is your primary key.

answered Jul 24 '12 at 20:10

Majid Fouladpour

26,043
19
66
124

2

I got this: `CREATE TABLE stocks ( name varchar(10) NOT N...` – irosenb Jul 24 '12 at 20:17
This should show you what the primary key and what the unique key is. – Joshua Dance May 27 '14 at 20:58

score 8 · Answer 3 · edited Feb 04 '16 at 03:24

8

Make sure PRIMARY KEY was selected AUTO_INCREMENT.
Just enable Auto increment by :
ALTER TABLE [table name] AUTO_INCREMENT = 1
When you execute the insert command you have to skip this key.

edited Feb 04 '16 at 03:24

chw21

7,378
1
12
26

answered Feb 04 '16 at 02:34

Đoàn Nhật Duẩn

81
1
2

score 3 · Answer 4 · edited Dec 09 '15 at 02:50

3

I solved it by changing the "lock" property from "shared" to "exclusive":

ALTER TABLE `table` 
CHANGE COLUMN `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '' , LOCK = EXCLUSIVE;

edited Dec 09 '15 at 02:50

davejal

5,431
10
33
73

answered Dec 09 '15 at 02:19

Hegle Leonardo Melgar

31
1

score 2 · Answer 5 · answered Jul 24 '12 at 20:11

What is the exact error message? #1062 means duplicate entry violating a primary key constraint for a column -- which boils down to the point that you cannot have two of the same values in the column. The error message should tell you which of your columns is constrained, I'm guessing "shares".

score 1 · Answer 6 · edited May 05 '15 at 17:13

Probably this is not the best of solution but doing the following will solve the problem

Step 1: Take a database dump using the following command

mysqldump -u root -p databaseName > databaseName.db

find the line

ENGINE=InnoDB AUTO_INCREMENT="*****" DEFAULT CHARSET=utf8;

Step 2: Change ******* to max id of your mysql table id. Save this value.

Step 3: again use

mysql -u root -p databaseName < databaseName.db

In my case i got this error when i added a manual entry to use to enter data into some other table. some how we have to set the value AUTO_INCREMENT to max id using mysql command

I got the error Unrecognized statement type. (near "ENGINE" at position 0) — wp-overwatch.com, Apr 10 '18 at 05:05

score 0 · Answer 7 · answered Apr 14 '17 at 20:03

0

Repair the database by your domain provider cpanel.

Or see if you didnt merged something in the phpMyAdmin

answered Apr 14 '17 at 20:03

Guy

1

score 0 · Answer 8 · answered Oct 30 '20 at 09:01

The DB I was importing had a conflict during the import due to the presence of a column both autoincrement and primary key.

The problem was that in the .sql file the table was chopped into multiple "INSERT INTO" and during the import these queries were executed all together.

MY SOLUTION was to deselect the "Run multiple queries in each execution" on Navicat and it worked perfectly

score -2 · Answer 9 · answered Jun 03 '17 at 19:50

I had this error from mySQL using DataNucleus and a database created with UTF8 - the error was being caused by this annotation for a primary key:

@PrimaryKey
@Unique
@Persistent(valueStrategy = IdGeneratorStrategy.UUIDSTRING)
protected String id;

dropping the table and regenerating it with this fixed it.

@PrimaryKey
@Unique
@Persistent(valueStrategy = IdGeneratorStrategy.UUIDHEX)
protected String id;

#1062 - Duplicate entry for key 'PRIMARY'

9 Answers9

Linked