How to restrict input to only whole numbers in Oracle?

Question

I am making a naive ratings platform db and I want to restrict a rating to be whole numbers from 1 to 5. To clarify I do not want to round or truncate, I want it to show a constraint violation if anything except 1,2,3,4,5 is entered.

The data type I'm using is smallint for rating. If I input 2.7, say, it truncates to 2 and proceeds to add the relation instance to the table. Which I don't want. How can I add a constraint to prevent this?

check this https://stackoverflow.com/questions/63079959/oracle-constraints-datatype — Roberto Hernandez, Jul 24 '20 at 20:59
you can't with a constraint . smallint is rounding up the value internally. I post in the other question a solution which does the trick — Roberto Hernandez, Jul 24 '20 at 20:59

score 0 · Answer 1 · answered Jul 24 '20 at 21:00

0

This is what the CHECK keyword is for (https://www.w3schools.com/sql/sql_check.asp)

If you had a table

CREATE TABLE Reviews (
     Rating smallint CHECK (Rating IN (1,2,3,4,5)
);

answered Jul 24 '20 at 21:00

DownloadPizza

3,307
1
12
27

that does not work for smallint, the value is round up, so I could insert 1.1, 1.2, 1.5. – Roberto Hernandez Jul 24 '20 at 21:03
Oh I see, thats a problem – DownloadPizza Jul 24 '20 at 21:04
I post a solution to this in the other question, which does the trick but it is quite complex – Roberto Hernandez Jul 24 '20 at 21:04

How to restrict input to only whole numbers in Oracle?

1 Answers1