Oracle - datatype to store number of a specific length
oracle, sql, sqldatatypes, types
Solution
It depends.
Is it a number? Will you do mathemtical operations on the number? Will you sort it numerically? If yes, then store it as a number. You should also create a constraint to ensure that the number always has 8 digits:
number_field number(8) check (number_field between 11111111 and 99999999)
If it's more like a code (like a credit card number, social security number or account number), I think I would use char(8), and a check constraint to ensure that it contains only numbers (a regular expression is a very good use for this). By using a char datatype rather than a varchar you don't need to make sure that the field contains 8 characters, just that the characters are all digits.
code_field char(8) check (regexp_like(code_field, '[:digit:]{8}'))
In effect, what you want are domains but Oracle doesn't have them.
As to whether to validate client-side or server-side, in my world, I always rely on database constraints to ensure that the data stored in the database is as clean as possible. In my apps, I test for constraint violation to return an appropriate error to the user. Of course, you can do validation client-side too, but why repeat yourself? The only case is when you want to avoid a round trip to the server.
Problem
I'm trying to figure out what datatype I should use in my database to store a fixed 8 digits number. Should I use `char(8)` even though it accepts other characters and validate on the application side or there is something like `number(x)` that could make it ? I've got the same dilemma with the phone number column. Should I use `char(x)` or something would be more appropriate ? Edit - Answers to your questions: - the first fixed 8 digits column will most likely be used as a primary key. It represents to social security number in my country. No arithmetic will be done on this column. I need to keep all digits (even possible leading zeros). Although, it will probably sorted (since it'll be a primary key) - For the phone number I was going to store it as a string like this : `12345678901`. The question was more about "How can I make sure that this `char[]` will not accept letters or other characters. Should this validation be made client-side ?