mysql duplicate entry with different values

Question

I am running the two following queries on a table where the refId table is unique. As you can see the refId values that are going in are different but I am getting the following error

#1062 - Duplicate entry '2147483647' for key 2

INSERT INTO eve_journal(`date`, `refID`, `refTypeID`, `ownerName1`, `ownerID1`, `ownerName2`, `ownerID2`, `argName1`, `argID1`, `amount`, `balance`, `reason`) VALUES('2011-07-12 00:56:00','4597105986','85','CONCORD','1000125','Qzack','90288778','Josameto','30000156','12956.00','365344321.02','11717:3,17057:1,17060:2,17065:1,17076:2,17081:2,23989:2,')


INSERT INTO eve_journal(`date`, `refID`, `refTypeID`, `ownerName1`, `ownerID1`, `ownerName2`, `ownerID2`, `argName1`, `argID1`, `amount`, `balance`, `reason`) VALUES('2011-07-12 00:52:00','4597093172','34','Shigakarri Niromochi','3012152','Qzack','90288778','Shigakarri Niromochi','3012152','18400.00','365331365.02','No reason specified.')

Perhaps I do not understand what unique and duplicate mean or maybe I am missing the point of a unique table. To help guide your answers, I have the refID set to unique because it is the only thing other than the day that is getting put in that is guaranteed to be different. How ever with the way the data is pulled, it is possible to pull the same entry more than once. I want to prevent duplicate entries and figured I could do it with mysql saying no.

Answer 1

Looks like the data type on your refId column isn't large enough so it's getting shrunk down to the max size of an INT. Increase that either to an UNSIGNED INT or to a BIGINT.

Answer 2

The error state that the duplicate value is 2147483647, which is the maximum value of the field as declared in the DB. the two values to insert are larger and are probably capped to the max.

Solve it by increasing the field size on the DB