Sql table adding constraint to check for number limit

Question

I am creating a table in oracle DB and trying to add constraint so that the numbers allowed in the column are 1,2,3.

CREATE TABLE "TABLE_EXAMPLE"
(
.
.
"PROTOCOL" NUMBER (1,2,3),

....)

CONSTRAINT "CH1"
        CHECK ("PROTOCOL" BETWEEN 1 AND 3),

Am I doing right or any better way?

`NUMBER(0,1)` isn't an enumeration. It's the number of digits, and the precision. — Plouf, Jan 22 '13 at 11:45
@Plouf! `number(0, 1)` is simply wrong declaration, because numeric precision cannot be zero. — Nick Krasnov, Jan 22 '13 at 11:53

wolφi · Accepted Answer · 2013-01-22T11:51:28.340

5

CREATE TABLE TABLE_EXAMPLE 
(
 ...
  PROTOCOL NUMBER(1) NOT NULL CONSTRAINT CH1 CHECK (PROTOCOL IN (1,2,3))
 ...
);

BETWEEN 1 AND 3 includes 1.5, 1.6, etc.

And I'd recommend not to use quotes " unless you have special characters in table or column names...

edited Jan 22 '13 at 11:51

answered Jan 22 '13 at 11:45

wolφi

8,091
2
35
64

whats the difference between NUMBER(1) and NUMBER(0,1) ? – constantlearner Jan 22 '13 at 11:46
1

The data type `NUMBER` takes two parameters `NUMBER(p,s)`. The first one, `p`, is the precision, the second one, `s` is the scale. There are some examples in the documentation http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#sthref117 – wolφi Jan 22 '13 at 11:56

score 2 · Answer 2 · answered Jan 22 '13 at 11:42

2

If you are going to check in the table level Check Constraint is the best way. because if you are inserting larger value then the check constraint ll throw the error.

answered Jan 22 '13 at 11:42

Dileep

624
3
10
20

Sql table adding constraint to check for number limit

2 Answers2