Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why is the following allowed

INSERT INTO c VALUES (123.2,'abcdefghijklmnopqrstuvwxyzabcdef', 'abcdefghijklmnop');

When table contains

CustomerID      NUMBER(3,0) -- That's the 123.2 entry

In other words, total of 3 digits with 0 after floating point?

If number is not a way to go, how would you enforce full integer only (no floating point)

share|improve this question

3 Answers 3

up vote 3 down vote accepted

When you define a column as number(m,n) where m is larger than n and n is positive, the values you can store in it can have m-n digits to the left from period and n digits to the right after period. For example when you defined your column as number(5,3), these are valid values: 1,234, 12,345. If you attempt to insert a value with a precision higher than n, it is automatically rounded, for example 1,2345 is rounded to 1,235.

When you define a column as number(m,0) you can have m digits to the left before the decimal point, and the numbers you insert are automatically rounded.

When you define a column as number(m,n) where n is larger than m, the values you can store in it can have zero digits to the left from the period and n digits to the right from the period with n-m zeros right after the decimal point. For example, if you defined your column as number(3,5), you can have values like 0.00123 in it, but not 0.01200. If you insert the value with more than n digits like 0.001235 it's automatically rounded to 0.00124.

When you define a column as number(m,n) where n is negative then it can store m-n digits to the left from decimal point, there should be exactly abs(n) zeros to the left from the decimal point, and the number is rounded to the last abs(n) places. For example, if you defined your column as number(3,-1), you can store numbers with 3 - -1 = 4 digits before decimal point which should have zeros in the last abs(n)=1 places. You can have values like 280, 1230, and if you isert values with digits other than zero in the last abs(n) places, the numbers are automatically rounded, for example 536 will eventually be stored as 540, 1245 will be stored as 1250.

share|improve this answer
    
In the other post it's suggested to use CHECK constraint. But if constraints for some reason are disabled—manually (for example during overnight bulk load of data), or automatically (for example as a result of Direct Path load with SQL*Loader—in that case, CHECK and FOREIGN KEY constraints are disabled)—the table may be left with new values in it which violate the disabled constraints. I think the safest way is to define the column as NUMBER(3), which is just a shorthand for NUMBER(3,0). –  Yasir Arsanukaev Apr 8 '13 at 4:11

Jam,

As an answer to your question as to how would you enforce full integer only, the best way to achieve this, in oracle, would be to define a CHECK constraint on the column with a condition like "num = trunc(num)"

share|improve this answer
    
I'm doubting this is the best way to go, see the explanation in the comment to my answer. –  Yasir Arsanukaev Apr 8 '13 at 3:52

SQL standards have always allowed the implementation to determine what happens when any operation causes the least significant digits to be lost.

The 1992 standard says, "If least significant digits are lost, implementation-defined rounding or truncating occurs with no exception condition being raised."

The obvious (and correct) way to implement an integer column in standard SQL is to declare the column to be of type integer, or a type that's compatible with an integer. The "Oracle way" is to use number(38).

Source: ANSI, DB2, and SQL/DS Datatypes

I'd only point out that number(38) is also the data type Oracle recommends as a replacement for SMALLINT. I think it's prudent to make certain that number(n) is reasonable and accurate in your own schema. For example, you might need a CHECK constraint to restrict the range to match exactly the range of SMALLINT on a different dbms.

That would also apply to other exact numeric types, like BIGINT and TINYINT (SQL Server), SMALLINT (PostgreSQL), etc.

It's probably worth pointing out that Oracle will execute this SQL DDL without error.

create table test (
  test_id integer primary key
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.