9 Replies Latest reply: Mar 29, 2009 2:22 PM by John Spencer RSS

    NUMBER vs INTEGER for oracle 10g

    683556
      If I do not care that much about space, which data type should I choose between NUMBER and INTEGER for a primary key of a table? Are we supposed to use INTEGER or we are preferred to use NUMBER?
        • 1. Re: NUMBER vs INTEGER for oracle 10g
          JustinCave
          There is no difference-- INTEGER is just an alias for NUMBER(38).
          SQL> create table intTest( col1 integer, col2 number );
          
          Table created.
          
          SQL> desc intTest
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
          
           COL1                                               NUMBER(38)
           COL2                                               NUMBER
          It may be more clear to people that read your scripts if you use INTEGER for columns that must be integers and NUMBER (with appropriate scale) for fields that contain decimal values. But that is a very picky thing-- I doubt 1 in 100 people would care much.

          Justin
          • 2. Re: NUMBER vs INTEGER for oracle 10g
            6363
            You use integer when you want to constrain the column so that it cannot store decimals, that is the only difference.
            • 3. Re: NUMBER vs INTEGER for oracle 10g
              Hoek
              I already mentioned: it really doesn't matter...
              You want to use it as a substitute for a BOOLEAN.

              So: it's just a 'flag'-column, one character, what puzzles you, why bother and repost your question?
              There has to be a deeper requirement/question...
              Or you have some 'problem' and if that's the case you need to be more specific.

              What is your goal/problem?
              • 4. Re: NUMBER vs INTEGER for oracle 10g
                683556
                Why do you think I want to make a boolean field to be the primary key?
                • 5. Re: NUMBER vs INTEGER for oracle 10g
                  Hoek
                  Well, you tell me, it sounds...dangerous... a primary key is NOT a 'switch'.
                  A primary key is supposed to NEVER change, to be UNIQUE....else it's NOT a primary key.
                  What's your logic here....


                  Here's why: Re: What is the best way to represent BOOLEAN in oracle 10g?

                  Edited by: hoek on Mar 27, 2009 8:32 PM
                  • 6. Re: NUMBER vs INTEGER for oracle 10g
                    683556
                    This primary key is just an identity field. I suppose I should use INTEGER from the above replies to this thread.
                    • 7. Re: NUMBER vs INTEGER for oracle 10g
                      Hoek
                      Don't get me wrong: I 'm just asking why you're so puzzled about the choice of datatype, that's a good thing imo.
                      But if one's puzzled, there's a reason.
                      There's nothing wrong with INTEGER or WHATEVER.
                      As long as you know what the question is, what people expect you to do/deliver, and you know the requirement, and you KNOW what datatype to use. From the input you gave sofar: it doesn't matter.
                      • 8. Re: NUMBER vs INTEGER for oracle 10g
                        107829
                        I'm not sure that is what the thread says. I read it as people saying that it is a personal preference and if you say integer for a column in a table, it becomes number anyway. Personally, I'd rather use number(38) and then only use 38 if I REALLY needed it to be 38. If you data only needs 4 digits today, then specify it as number(4). That would (to me) make it much easier for the person reading your ddl and/or working with your tables. Never make a column accept larger values than your application can handle. If the app expects 4 digits and it got 32, it would probably have some kind of problem.
                        • 9. Re: NUMBER vs INTEGER for oracle 10g
                          John Spencer
                          "Never make a column accept larger values than your application can handle"

                          On the other hand, never constrain a column that does not require constraining, and a numeric PK column is a prime example of a column that does not require a size constraint. I can't count all of the hours I have spent going through code because someone once said "We will never need more than X digits for Y field"

                          John