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

Question

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,'[email protected]',0872863492,1234);

select*from passenger_details;
+------------+--------------------+-----------+------+
| bank_card  | email              | mobile    | p_id |
+------------+--------------------+-----------+------+
| 2147483647 | [email protected]  | 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,'[email protected]',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 1

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.