Geek Answers Handbook

How to limit INTEGER length when creating a table in ORACLE?

everything

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

for example: RegNumber is INTEGER, and it must be an 8-digit number.

How to do this when creating a table?

+7
source share
2 answers

The INTEGER data type is just a subtype of NUMBER. You can define the column as NUMBER (8.0) to get an integer column that is <= 8 digits.

If you are trying to make sure that the column has 8 digits and ONLY 8 digits, you need to add a check constraint on the column:

CREATE TABLE RegTable (RegNumber NUMBER(8,0), CONSTRAINT CheckRegNumber CHECK (RegNumber > 9999999) );
+9
source

Just specify a length of 8 and an accuracy of 0. Like this

 SQL> create table t8 (col1 number(8,0)) 2 / Table created. SQL> insert into t8 values (12345678) 2 / 1 row created. SQL> insert into t8 values (123456789) 2 / insert into t8 values (123456789) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column SQL>

To ensure the exact length (all digits must be eight digits long), you need to use the CHECK constraint:

 SQL> alter table t8 2 add constraint t8_ck check (length(col1) = 8) 3 / Table altered. SQL> insert into t8 values (1234567) 2 / insert into t8 values (1234567) * ERROR at line 1: ORA-02290: check constraint (APC.T8_CK) violated SQL>
+2
source

More articles:


All Articles