Using Check Constraints with Regular Expressions

was something I just wanted to experiment with. Turns out they can be effective. The example here will address a common problem when requesting information from a user. One can argue that the this type of check should be done at the application layer and not the database layer, but for demonstration purposes it’s in the database.  Let’s get to it shall we?

For this example I am using Database 12c:

Create a table to hold an ID, NAME, and EMAIL and insert a row.

CREATE TABLE EMAIL_ADDY
(
“ID”     NUMBER
“NAME”   VARCHAR2(20 BYTE)
“EMAIL”  VARCHAR2(50 BYTE)
);

 

As you can see, ‘alton’ is not a legitimate email address, but without any validation checks, it is inserted into the database.

 

 

Now create the same table and add a Check Constraint, using a REGEXP Function, to the EMAIL column.

CREATE TABLE EMAIL_ADDY
(
“ID”     NUMBER
“NAME”   VARCHAR2(20 BYTE)
“EMAIL”  VARCHAR2(50 BYTE) CONSTRAINT email_format
CHECK (REGEXP_LIKE (email
, ‘^\w+(\.\w+)*+@\w+(\.\w+)+$’)
));

 

So far so good!  Our REGEXP_LIKE function allowed a legitimately formed email address to be inserted into the database.

 

 

 

Finally, let’s try that original insert:

There we have it. Next would be to trap this error and send something meaningful back to the user.