Oracle varchar2 minimum length

Question

In Oracle db we have a table with varchar2 type of column (for example USERNAME). How can I set a exact (or at least minimum) length for this column? So that all usernames inserted into this table can be only 10 (or have to be at least 10) characters long.

Answer 1

You could use a check constraint:

CREATE TABLE mytable (
  mycolumn varchar2(50),
  constraint strlen check (length(mycolumn) > 2)
)

Or something similar. I'm not sure how performant this is, though.

Answer 2

Or just for fun,

CREATE TABLE testit
( mycolumn VARCHAR2(20) CONSTRAINT min_length_chk CHECK (mycolumn LIKE '__%') );

It's less explicit than the LENGTH() approach though so I'm not sure I'm recommending it except as an idea for related issues.