Oracle 11g - Check constraint with RegEx

Question

I'm using Oracle 11g, and trying to create a table define constraints on the creation.

I was trying to add check constraint to validate some information (like e-mail address, phone number, etc...)

Is there something in Oracle 11g that would allow me to do something like this?

constraint CK_CONSTRAINT_NAME check (EMAIL like 'REGEX')

The regEx I wanted to use (grabbed from regexLib) is:

^[a-zA-Z][a-zA-Z0-9_\.\-]+@([a-zA-Z0-9-]{2,}\.)+([a-zA-Z]{2,4}|[a-zA-Z]{2}\.[a-zA-Z]{2})$

I think Oracle 11g (correct me if I'm wrong) doesn't support this format for RegEx...

I've seen methods using REGEX_LIKE, but it seems to only work in

WHERE

clauses.

I'd like to keep it as a check constraint and not a trigger or an external function/script.

Also, I've read in other threads here, someone saying RegEx' are not a good way of verifying e-mail address format and such information. No reason was given in the comment, and I'd like to know why, if a reason there is!

Thanks for your inputs and your help.

much appreciated.

Answer 1

A check constraint follows the same syntax rules as conditions for a WHERE clause:

alter table foo
  add constraint check_email 
  check (REGEXP_LIKE(email,'your_regex_goes_here','I'));

More details in the manual:

for Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141
for Oracle 12 - https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141

Edit:

There are however some restrictions on what you can actually use in a check constraint:

Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52205
Oracle 12 - https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52205

Oracle 11g - Check constraint with RegEx

Sign up