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!

Solution

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

Comments

[Pacane]

Maybe you can tell me, I've tried this : "alter table JOURNALISTE add constraint check_email check (REGEXP_LIKE(COURR_JOUR,'^[a-zA-Z][a-zA-Z0-9_\.\-]+@([a-zA-Z‌​0-9-]{2,}\.)+([a-zA-‌​Z]{2,4}|[a-zA-Z]{2}\‌​.[a-zA-Z]{2})$','I')‌​)" and I still can't enter a valid e-mail address in an insert statement. I took this RegEX on regEx lib, but I'm not sure it's supported by Oracle. I've read some parts of the documentation about regEX and it didn't seem to use this standard for numbers, it seemed to use something like :num or something similar...

[DavidRR]

It is not quite true that "anything that can be used in a WHERE condition can be used in a check constraint." For example, a check constraint cannot refer to columns in other tables, cannot make calls to functions that are not deterministic, and cannot make calls to user defined functions. See Restrictions on Check Constraints [11g Release 2 (11.2)].

[a_horse_with_no_name]

@DavidRR: good point, thanks. I was referring to the syntax, rather than the real expressions. I clarified my post.

[DanielM]

Links are broken :/

[Pushkr]

Please add explanations for your answers.