How to enforce email address column to accept only “valid” email values?

The easiest way to solve this problem is to add a check constraint with regular expression validation:

--Let's first add an email column to the emp table:

ALTER TABLE emp ADD email VARCHAR2(100);

Table altered.

--Now let's add constraint using REGEXP_LIKE function:

ALTER TABLE emp ADD CONSTRAINT chk_emp_email 
CHECK (REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'));

Table altered.

Now let’s see if it works:

UPDATE emp
SET email=ename || '@emp.com'
WHERE ROWNUM=1;

1 row updated.

UPDATE emp
SET email = 'abc@' --invalid email
WHERE ROWNUM=1;
UPDATE emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_EMP_EMAIL) violated

You may want to drop the new column after this exercise:

ALTER TABLE emp DROP COLUMN email;
Table altered.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s