CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Blurryface
Blurryface

Reputation: 344

Preventing decimals from entering into a column - Oracle

I was wondering if there's a way to prevent data of number type from entering into a table if the value of the number is not a whole number.

ie. If the data contains fractions or decimals, I do not want to store the data. I am using oracle 11g. Despite defining the scale as 0, oracle would round off the decimal part of the number to the nearest integer.

Pretty sure we'd have to have some sort of check constraint on the column, but am not quite sure what that would be.

Any help would be greatly appreciated. Thanks!

Upvotes: 1

Views: 1201

Answers (2)

Scott
Scott

Reputation: 5035

For smaller fractions, perhaps use float?

(since adding scale to the number only worked for the range defined)

drop table whole_numbers;
create table whole_numbers(a float);
alter table whole_numbers add constraint check_whole check (a = trunc(a));

insert into whole_numbers values(1);

1 row inserted.

insert into whole_numbers values(1.00002);

ORA-02290: check constraint (VBSMASTER.CHECK_WHOLE) violated
*Cause:    The values being inserted do not satisfy the named check

*Action:   do not insert values that violate the constraint.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use a check constraint:

alter table t add constraint chk_col_int as (col = trunc(col));

However, it might make more sense to just declare the column as an integer or a numeric with no decimal places. Hmmmm, that might not do what you want, because an input real/floating point number would be converted rather than generating an error.

Upvotes: 1

Related Questions