Menu
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?
352
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.
INTEGER provides 4 bytes of storage for integer values.
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.
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)
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
