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?
Advertisement
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.