Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to restrict the length of INTEGER when creating a table in ORACLE?

Tags:

sql

oracle

all

When creating a table in Oracle sql*plus, I want to restrict that the length of an INTEGER column can only be 8.

eg: the RegNumber is an INTEGER, and it must be a 8 digit number.

How can I do this when I creating a table?

like image 352
MengT Avatar asked Jan 12 '12 15:01

MengT


People also ask

How do you restrict the length of integer when creating a table in SQL?

If you want to limit the range of an integer column you can use a check constraint: create table some_table ( phone_number integer not null check (phone_number between 0 and 9999999999) ); But as R.T. and huMpty duMpty have pointed out: a phone number is usually better stored in a varchar column.

What is the length of integer in Oracle?

INTEGER provides 4 bytes of storage for integer values.

What is Max VARCHAR length Oracle?

The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32.


1 Answers

The INTEGER datatype is just a subtype of NUMBER. You can define the column as NUMBER(8,0) to get you an integer column that is <= 8 digits.

If you are trying to ensure that the column is 8 digits and ONLY 8 digits, you'll need to add a check constraint on the column:

CREATE TABLE RegTable
(RegNumber NUMBER(8,0),
CONSTRAINT CheckRegNumber  CHECK (RegNumber > 9999999)
);
like image 97
N West Avatar answered Oct 21 '22 12:10

N West