CODEWITHSUNDEEP
sqloracle-databaseddlcheck-constraints
Denisa M
Denisa M

Reputation: 11

Restricting the exact number of numbers input in a field Oracle SQL Developer

I am trying to make a field for telephone numbers that requires no more, no less than 10 numbers. I tried Telephone number(10) CHECK (Telephone=10) but it doesn't work, and neither does adding it as a constraint after the attributes.

Upvotes: 0

Views: 1264

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Telephone numbers are generally stored as strings, not numbers. First, the number is not really a number. It doesn't make sense, for instance, to add two of them together. Second, leading zeros can matter.

So, I would recommend:

telephone varchar2(10) check (length(telephone) = 10)

If you want to keep it as a number, you can use a range:

telephone number(10, 0) check (telephone >= 1000000000 and
                               telephone < 10000000000
                              )

This is explicit that there are no decimal places.

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use a check constraint, like so:

create table t (id int primary key, telephone varchar2(10), check(length(telephone) = 10))

Or if telephone number is an integer:

create table t (
    id int primary key, 
    telephone int, 
    check(telephone>= 1000000000 and telephone < 10000000000)
);

Upvotes: 1

Related Questions