views:

30

answers:

1

I have the following table:

Table Account{
    [...]
    email varchar(100),
    [...]
}

and a corresponding regular expression:

/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i

How can I use the MySQL model, to link the regular expression to the the data-field "email", so that the regex is accessible to read out through php as well as a trigger or even better, in a kind of constraint?

I want to define this directly using MySQL workbench, so a separate table won't work for me.

+2  A: 

In my opinion, input validation like this is not really the task of the database. Those validation rules may change. You may even make them configurable by the user some day. This becomes awfully difficult if the rules are buried in some trigger somewhere.

Also, you will need to handle a validation error on application level anyway - showing a message of some sort.

The best place to make them accessible to PHP inside mySQL may be a column comment, that you can fetch using SHOW COLUMNS in conjunction with the FULL keyword.

Pekka
Thank you for your suggestion. Is there a common pattern how to do something like this in the comments? My first thought about this is to store a JSON object in the comment to be able to expand it later.
Thomas
@Thomas I'm not aware of a common pattern; something like a JSON object sounds like a good idea. I can't find a maximum size for columns [here](http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html) - maybe test with an extremely large value first to avoid problems
Pekka
Thank you very much!
Thomas