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)
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.
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(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.
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.
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.