Search code examples
sqloracletypessqldatatypes

Oracle - datatype to store number of a specific length

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:

  1. 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)
  2. 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 ?
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.