How to allow letters only in VARCHAR2 column (Oracle SQL)

Question

I have a column "surname" in my Oracle SQL database.

CREATE TABLE test ( id VARCHAR2(3) PRIMARY KEY, surname VARCHAR2(10));

What should I add in the query above, to allow inserting only alphabetical characters in a "surname"?

Answer 1

You need to have a CHECK constraint in your table.

The following should suffice CHECK (regexp_like(surname,'^[[:alpha:]]+$')). Where [[:alpha:]] is alphabet character class. Thus, only considers the alphabets.

Let's see a test case -

SQL> CREATE TABLE TEST(
  2  surname VARCHAR2(10),
  3  CONSTRAINT constraint_name CHECK (regexp_like(surname,'^[[:alpha:]]+$'))
  4  )
  5  /

Table created.

SQL>
SQL> INSERT INTO TEST values ('LALIT')
  2  /

1 row created.

SQL>
SQL> INSERT INTO TEST values ('123')
  2  /
INSERT INTO TEST values ('123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>

So far it works good. Let's check with Alphanumeric values.

SQL> INSERT INTO TEST VALUES ('LALIT123')
  2  /
INSERT INTO TEST VALUES ('LALIT123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>
SQL> SELECT * FROM TEST
  2  /

SURNAME
----------
LALIT

SQL>

So, the CHECK constraint ONLY allows the alphabets.