CODEWITHSUNDEEP
oracle-databasenumberscreate-table
GivenPie
GivenPie

Reputation: 1489

How can I max value and min value for a number data type in the creation of a table in oracle

I want to set score number to be maximum 100 and minimum 0. Am I right with just giving score number 3 placements, 3 digits and that is already enough as long as I don't looks for a number larger than 100 with NO decimal places. 

create table grades (
S varchar2(12),
C varchar2(10),
Score number(3),
Letter_Grade char(1)
Constraint PK_grades Primary Key (S)
)

Upvotes: 3

Views: 4614

Answers (3)

Jordan Parmer
Jordan Parmer

Reputation: 37204

You need to use a check constraint:

create table myTable (
      a number check (a between 0 and 100),
      b number
    );

Upvotes: 5

APC
APC

Reputation: 146289

Do you want to allow decimal places? If not specify the precision too

Score number(3, 0),

The other thing is, number(3) will allow a range of -999 to 999. So you need to add a check constraint. Something like this 

constraint CK_grades check ( score between 0 and 100 )

Upvotes: 2

cagcowboy
cagcowboy

Reputation: 30858

Yes, a NUMBER(3) would work (assuming integers only)

You could also add a CHECK constraint on the column.

ALTER TABLE GRADES ADD CONSTRAINT GRADES_SCORE_CHECK CHECK (SCORE BETWEEN 0 AND 100);

Upvotes: 1

Related Questions