I have a table: 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: 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

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

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

​x
 
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)

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

 
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?

Answer

INT has a maximum signed value of 2147483647. Any number greater than that will be truncated to that value. What you need to do is change that column to be a varchar(20) which will hold strings of up to 20 characters since bank card numbers are strings and not actually numbers (you don’t do math with the). You also should remove AUTO_INCREMENT as that is not a value you will be incrementing.

Advertisement

Answer