Is it Possible to Enforce Data Checking in MySQL using Regular expression

Question

Suppose I have an attribute called phone number and I would like to enforce certain validity on the entries to this field. Can I use regular expression for this purpose, since Regular Expression is very flexible at defining constraints.

Galantine

Short answer: Yes you can. Could you add more context ? Would you like to filter it at input ? If so why don't you filter when the user signs-up for example ? – Out

@HamZaDzCyberDeV No, not at input. I want to enforce this in the database. I understand it can be done by in the application layer, I was wondering if the same was possible in the database layer when creating the tables. – Galantine

When creating a table, you have to specify a column type, there is no "phone number" type. You may want to look at triggers – Out

Answer 1

Yes, you can. MySQL supports regex (http://dev.mysql.com/doc/refman/5.6/en/regexp.html) and for data validation you should use a trigger since MySQL doesn't support CHECK constraint (you can always move to PostgreSQL as an alternative:). NB! Be aware that even though MySQL does have CHECK constraint construct, unfortunately MySQL (so far 5.6) does not validate data against check constraints. According to http://dev.mysql.com/doc/refman/5.6/en/create-table.html: "The CHECK clause is parsed but ignored by all storage engines."

You can add a check constraint for a column phone:

CREATE TABLE data (
  phone varchar(100)
);

DELIMITER $$
CREATE TRIGGER trig_phone_check BEFORE INSERT ON data
FOR EACH ROW 
BEGIN 
IF (NEW.phone REGEXP '^(\\+?[0-9]{1,4}-)?[0-9]{3,10}$' ) = 0 THEN 
  SIGNAL SQLSTATE '12345'
     SET MESSAGE_TEXT = 'Wroooong!!!';
END IF; 
END$$
DELIMITER ;


INSERT INTO data VALUES ('[phone]'); -- should be OK
INSERT INTO data VALUES ('+64-22122 WRONG 1442'); -- will fail with the error: #1644 - Wroooong!!! 

However you should not rely merely on MySQL (data layer in your case) for data validation. The data should be validated on all levels of your app.

Bakst

Thanks. This is something I was looking for. I understand that all validation should be done at all layers, but I needed to know how it's done for data layer. – Galantine

This is the create table query: create table fk ( empid int not null unique, age int check(age between 18 and 60), email varchar(20) default 'N/A', secondary_email varchar(20) check(secondary_email RLIKE'^[a-zA-Z]@[a-zA-Z0-9]\.[a-z,A-Z]{2,4}'), deptid int check(deptid in(10,20,30)) ) ; \n This INSERT query will work:\n insert into fk values(1,19,'[email protected]','[email protected]', 30);\n This INSERT query will not work:\n insert into fk values(2,19,'[email protected]','[email protected]', 30); – Acquah

So we can set regular expression in the Check Constraint of create table. – Acquah

Answer 2

MySQL 8.0.16 (2019-04-25) and MariaDB 10.2.1 (2016-04-18) now not only parse CHECK constraint but also enforces it.

MySQL: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

MariaDB: https://mariadb.com/kb/en/constraint/

Stig

Answer 3

Actually, we can can set regular expression within check constraints in MySQL. Eg.,:

create table fk 
  ( 
    empid int not null unique, 
    age int check(age between 18 and 60), 
    email varchar(20) default 'N/A', 
    secondary_email varchar(20) check(secondary_email RLIKE'^[a-zA-Z]@[a-zA-Z0-9]\.[a-z,A-Z]{2,4}'), 
    deptid int check(deptid in(10,20,30)) 
  ) 
; 

This INSERT query will work: insert into fk values(1,19,'[email protected]','[email protected]', 30);

This INSERT query will not work: insert into fk values(2,19,'[email protected]','[email protected]', 30);

Acquah