2

I am running Oracle 11gR2 11.2.0.3. I have two tables:

  • table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480)
  • table2 contains a a column named SUBJECT_ID, whose datatype is NUMBER, and is a foreign key to table1.SUBJECT_ID.

Is there any point in specifying the precision and scale of table1.SUBJECT_ID (i.e. declaring it NUMBER(7,0) instead of simply NUMBER)?

The Oracle documentation advises to specify the precision and scale, as "it provides extra integrity checking on input". But in my case wouldn't the foreign key constraint take care of this integrity checking?

  • 1
    why are you wondering? Is there something you're not mentioning? – Max Vernon May 3 '15 at 2:25
  • @MaxVernon Just wondering if I haven't missed anything in my reasoning. In there is no point in specifying the precision and scale of a number for a foreign key, then I won't invest my time in making sure the schema contains NUMBER instead of ` NUMBER(7,0)`. – Franck Dernoncourt May 3 '15 at 2:26
  • 1
    It is always best to be explicit. In future, looking at the column definition will leave no doubt if you declare it as the type it actually is. – Max Vernon May 3 '15 at 2:32
  • @MaxVernon Thanks, I agree for explicitness. Do you see any other motivation to specify the precision and scale in this case? – Franck Dernoncourt May 3 '15 at 2:35
  • Computer programers and DBAs ought to be anal about consistency. I know I am. Make both sides identical in terms of datatypes, nullability (assuming a mandatory foreign key), name (where possible) and no one can wonder if there wasn't some slip-up. I'd add a comment on the column if the foreign key is optional. – Colin 't Hart May 5 '15 at 19:19
2

Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter.

Take this table:

CREATE TABLE T ( 
    x1 number,
    x2 number(6,3)
);

x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, you're taking up extra space that may not be needed.

  • 3
    number is a variable length data type. So x1 will take up to 21 bytes. If you store the number 42, it will take exactly the same space in x1 and x1 – a_horse_with_no_name May 5 '15 at 22:16
  • You might want to hit up Connor's link and repro it yourself. – Jeremiah Peschka May 5 '15 at 22:46
  • I agree with @a_horse_with_no_name. The linked page has as example the value of the expression 3*(1/3) which results (for some unknown reason due to the implementation) to different values, 1.000 or 1.0000....000, depending on the precision of the column: sqlfiddle.com/#!4/f68a4/1 – ypercubeᵀᴹ May 6 '15 at 0:15
  • And I might be wrong about the 1.00000...0000 : sqlfiddle.com/#!4/f68a4/13 – ypercubeᵀᴹ May 6 '15 at 0:26
  • I did read the the link and I'm surprised. It apparently has something to do how an expression is evaluated. Just wanted to add that a number column does not have a fixed size - similar to the way e.g. a varchar works. – a_horse_with_no_name May 6 '15 at 5:44

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.