0

We're doing database design and implementation work in my SQL course, and I accidentally made the value range too short. We're supposed to be building a DB for payment information, and when I put in the random CC number I made up, the system says "out of range value for column cardNumber". Is there a way I can alter the table to remake the data type INT(16) or something like that? (This is in MySQL if that's relevant)

2
  • 2
    Check the MySQL reference manual for available data types and the alter table statement. Also, don't store credit card "numbers" as numeric data types, because they are not numbers -- they are sequences of characters that happen to be digits.
    – mustaccio
    Nov 17 '21 at 20:46
  • I hadn't thought about it that way before, but it makes sense. Thanks for the input! Nov 18 '21 at 2:14
0

I agree with the comment above that you should use a string type column (for example CHAR(16)) for a credit card number, not a numeric.

MySQL's INT is always a 32-bit integer, supporting values from -2147483648 to 2147483647. The optional "length" argument is frequently misunderstood. It only affects the display width, not the range of values supported in the column. A column defined as INT(1) or INT(9) or INT(28) are all the same size.

The display width is irrelevant unless you use the ZEROFILL option, so values are padded with zeroes when you fetch the value. It doesn't change the size of the data type. The padding zeroes are not stored, they are added as the result set is fetched.

mysql> create table mytable (i1 int, i2 int(8) zerofill, i3 int(16) zerofill);

mysql> insert into mytable values (42, 42, 42);

mysql> select * from mytable;
+------+----------+------------------+
| i1   | i2       | i3               |
+------+----------+------------------+
|   42 | 00000042 | 0000000000000042 |
+------+----------+------------------+

ZEROFILL has been deprecated in MySQL 8.0.17, because it is so confusing.

1
  • Thanks for the answer, I get it now Nov 18 '21 at 2:15
1

By CC, do you mean "credit card"? If so, I strongly recommend you use VARCHAR(16) CHARACTER SET ascii. Or maybe more than 16.

It is not a number; you will not be doing arithmetic with it. It is a string of characters; today it is only digits, but there is very little stopping it from including letters tomorrow.

Security is a big issue. You _really should hire a security consultant to explain to you why you should not be storing CC numbers and to explain what to do instead.

Fixing it:

ALTER TABLE tbl_name 
    MODIFY COLUMN cardNumber VARCHAR(16) CHARSET ascii;

This won't fix any inserts with bogus values because they overflowed, but it will make it possible to insert valid 'numbers'.

Do quote the CC values when inserting/testing/etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.