# How to enforce full integer (no floating point)?

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)

-

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`.

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

-
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)`.

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