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.
asked Apr 14, 2013 at 22:50
Shamim Hafiz - MSFT
22.2k44 gold badges122 silver badges183 bronze badges
-
1Short 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 ?HamZa– HamZa2013-04-14 23:25:42 +00:00Commented Apr 14, 2013 at 23:25
-
@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.Shamim Hafiz - MSFT– Shamim Hafiz - MSFT2013-04-14 23:37:38 +00:00Commented Apr 14, 2013 at 23:37
-
When creating a table, you have to specify a column type, there is no "phone number" type. You may want to look at triggersHamZa– HamZa2013-04-14 23:44:41 +00:00Commented Apr 14, 2013 at 23:44
Add a comment
|
3 Answers
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 ('+64-221221442'); -- 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.
3 Comments
Shamim Hafiz - MSFT
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.
Shubhankar Raj
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);
Shubhankar Raj
So we can set regular expression in the Check Constraint of create table.
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
Comments
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);
