ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

user_3052893 0
Viewed 24466

I have a table:

CREATE TABLE passenger_details
   (
    bank_card INT(20) NOT NULL AUTO_INCREMENT,
    email VARCHAR(20), 
    mobile INT(15) NOT NULL,
    p_id INT NOT NULL,    
    PRIMARY KEY (bank_card),
    FOREIGN KEY (p_id) REFERENCES passenger(p_id) 
    );

INSERT INTO passenger_details 
VALUES (0123012301230123,'blah_@hotmail.com',0872863492,1234);

select*from passenger_details;
+------------+--------------------+-----------+------+
| bank_card  | email              | mobile    | p_id |
+------------+--------------------+-----------+------+
| 2147483647 | blah_@hotmail.com  | 872863492 | 1234 |
+------------+--------------------+-----------+------+
1 row in set (0.00 sec)

As we can see, the previous value, just went wrong into table. Should be 16 numbers and not only 10, actually different numbers.
When i try to insert a new value:

INSERT INTO passenger_details 
VALUES (1234258431681842,'blah@icloud.com',0895764829,123548);

I get this error:

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

If bank_card is AUTO_INCREMENT why there is error? Should I change the PK to another place?

mysql sql
3 Answers

Something to ask yourself. How did the number get that big? I did not insert 2 billion rows!

Well, possibly you 'burned' that many AUTO_INCREMENT ids. This can happen in man ways:

INSERT IGNORE ... -- when the insert is ignored (because it the row already exists)
REPLACE
IODKU

and probably others.

user_17668310

With MySQL phpmyadmin panel , this is how I got rid from this issue

>Go to your Table's structure from  phpmyadmin panel
>> select that PRIMARY column , click on change
>>> Change Column type to "BIGINT" and Attributes to "unsigned"

with other SQLs > change column attributes to "BIGINT unsigned" that worked for me

user_43378080
Related