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!

Nympho

That regex should be taken out and shot. I hope it's not really like that on RegexLib. "alum"? Useless quantifiers? Missing punctuation? Aside from that, regex is not good for validating e-mail addresses because the format is too variable for a reliable regex (you'll always miss valid mails and accept invalid ones). The best you can do is check for the presence of an @ sign, and try to send a confirmation e-mail to it. If that succeeds, and if you get a return mail, you know the address is valid and active. – Mincemeat

@TimPietzcker Sorry, I changed the regEx for the actual one, It was kind of a mash up of the original one and something I tried from Oracle documentation. – Nympho

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

Seignior

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-Z0-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... – Nympho

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)]. – Cesena

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

Links are broken :/ – Ken

Answer 2

CREATE TABLE MYTABLE(
  EMAIL VARCHAR2(30) CHECK(REGEXP_LIKE (EMAIL,'^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'))
);

ALTER TABLE MYTABLE ADD(CONTACT NUMBER(10) CHECK(REGEXP_LIKE(CONTACT,'[0-9]{10}')));


Explanation of Regular Expression
^           #start of the line
  [_A-Za-z0-9-]+    #  must start with string in the bracket [ ], must contains one or more (+)
  (         #  start of group #1
    \\.[_A-Za-z0-9-]+   #     follow by a dot "." and string in the bracket [ ], must contains one or more (+)
  )*            #  end of group #1, this group is optional (*)
    @           #     must contains a "@" symbol
     [A-Za-z0-9]+       #        follow by string in the bracket [ ], must contains one or more (+)
      (         #      start of group #2 - first level TLD checking
       \\.[A-Za-z0-9]+  #        follow by a dot "." and string in the bracket [ ], must contains one or more (+)
      )*        #      end of group #2, this group is optional (*)
      (         #      start of group #3 - second level TLD checking
       \\.[A-Za-z]{2,}  #        follow by a dot "." and string in the bracket [ ], with minimum length of 2
      )         #      end of group #3
$           #end of the line

Lanitalank

Please add explanations for your answers. – Threequarter