Ny point in specifying the precision and scale of a number for a foreign key

data integritydatatypesforeign keyoracle

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?

Best Answer

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.

Related Solutions

Ny point in undersizing VARCHAR columns

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale

The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.

Using mysql 5.5.20 the below returns 0.999:

SET @val = (SELECT CAST(1 AS DECIMAL(3,3)));
SELECT @val;

Whereas the following returns 1:

SET @val = (SELECT CAST(1 AS DECIMAL(3,0)));
SELECT @val;

I hope this helps.

EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:

DECLARE @val DECIMAL(3,3);
SET @val = 1;
SELECT @val;

Best Answer

Related Solutions

Ny point in undersizing VARCHAR columns

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale

Related Question