CODEWITHSUNDEEP
phpmysqldatabasephpmyadminkey
theGreenCabbage
theGreenCabbage

Reputation: 4845

Receiving a #1062 duplicate error in mySQL/phpMyAdmin despite not having a duplicate

I currently have a database called Spreadsheet with 2291 rows, each with 6 columns.

I also have a .csv file with 1000 more of these rows with the same 6 columns, though three of them are set default to NULL (same as some of the data in my database, meaning, some are set to NULL as default too). I was trying to import them (not replace) into the existing database.

The first columns is the primary key, and I know both the .csv and database do not have duplicate primary keys. The primary key looks something like this: 0015000000b0Y2u

My question is: how do I import these 1000 more rows (which come with unique primary keys themselves) into the pre-existing 2291 rows without getting the #1062 error? 

SQL query:

INSERT INTO  `Spreadsheet` (  `accountID` ,  `accountName` ,  `website` ,  `rating` ,  `imageURL` ,  `comments` ,  `category` ) 
VALUES (

'0015000000b0Y3z',  'Kittredge and Associates Inc',  'kittredgeandassociates.com', NULL ,  'kittredgeandassociates.com.jpg', NULL , NULL
)
MySQL said: 

#1062 - Duplicate entry '0015000000b0Y3z' for key 'PRIMARY'

Attached above is the #1062 error I have been receiving, despite being 100% sure that I do not have a duplicate key for PRIMARY.

I do not want to have mySQL autoincrement, as I have looked into that, and it is not the solution I am looking for.

I have tried changing the duplicate entry offender key, but to no avail, same error. Could someone lend me a hand?

Upvotes: 0

Views: 2114

Answers (2)

spencer7593
spencer7593

Reputation: 108530

I suspect the issue is that your primary key is a character column with a case insensitive collation (e.g. latin1_swedish_ci). If so, lower case and upper case letters are considered to be "equal", which would lead to a duplicate.

Here's a demonstration. Note the difference in the value of collation_name:

CREATE TABLE t3 (mycol VARCHAR(4) COLLATE latin1_swedish_ci PRIMARY KEY );
INSERT INTO t3 VALUES ('A'),('a');

-- Error Code: 1062
-- Duplicate entry 'a' for key 'PRIMARY'

CREATE TABLE t4 (mycol VARCHAR(4) COLLATE latin1_bin PRIMARY KEY );
INSERT INTO t4 VALUES ('A'),('a');

-- 2 row(s) affected

If you need MySQL to consider uppercase and lowercase letters to be unequal, then you need to specify either a case sensitive or a binary collation, rather than a case insensitive collation, for that column.

(NOTE: MySQL names collations that are case insensitive with a _ci on the end of the collation name, e.g. latin1_swedish_ci.)

If the column is currently latin1 characterset and latin1_swedish_ci collation, you probably want to change the collation on the column to be either latin1_general_cs or latin1_bin.

e.g.

ALTER TABLE t3 CHANGE mycol mycol VARCHAR(4) COLLATE latin1_general_cs ;

http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html

http://dev.mysql.com/doc/refman/5.5/en/adding-collation.html

Upvotes: 3

ficuscr
ficuscr

Reputation: 7073

Not sure how you would get this error if you are correct that the PK and any other unique indexes are not being violated. A solution might be to use ON DUPLICATE KEY UPDATE or the IGNORE keyword.

Upvotes: 0

Related Questions