How to enforce full integer (no floating point)?

Question

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)

Answer 1

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.

Answer 2

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)"

Answer 3

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
);