Oracle DB - Set Input Number to exact length

Question

I am trying to set a constraint where user are only allowed to input 11 digits into the Number Data Type (Phone Number). I've tried

alter table "booyeah" add constraint
"booyeah_CC1" check ( LENGTH("PHONE_NO") = 11)
/

and

alter table "booyeah" add constraint
"booyeah_CC1" check ( PRECISION("PHONE_NO") = 11)
/

but got an error. For the first one, I kept getting error because it's not detecting the character length, while the second one, gave me an invalid identifier.

Thanks for the help!

Answer 1

Set the data type for that column to varchar(11). If it must be exactly 11 characters every time, a check constraint will guarantee that: check (length(phone_no) = 11). To guarantee length and "numerality" (all digits, no letters), use

check (length(phone_no) = 11 and 
       regexp_like(phone_no, '^[[:digit:]]{11}$')
)

If you have to use a numeric type--and this is a bad idea--your best bet is probably numeric(11,0).

A check constraint can help you restrict the range of valid input, but no numeric types store leading zeroes. You'll have to jump through unnecessary and avoidable hoops if something like 00125436754 is a valid phone number.