I was thinking the other day how great it would be if you could store a regex pattern requirement in the database for each column. After a few Google searches I came across a post on the PostgreSQL Novice email list that gives an example of how to do this using PostgreSQL’s Check Constraint feature in combination with the POSIX Regular Expression support in PostgreSQL. Here is an insanely simple example:
CREATE TABLE example_table (
first_name VARCHAR(100)
CONSTRAINT check_first_name
( first_name ~* '^[a-z]+$')
);
That would create a table named ‘example_table’ with one column, called ‘first_name’, that would only accept inserts or updates if ‘first_name’ only contained lower case letters a through z. I love it when something pops into my head that I think would be really cool and then find out that someone else already has it working! (Although having PCRE would have been an added bonus.)
I also looked to see if MySQL supported this. It looks like the current versions do not, at least according to their create table syntax documentation or their constraints documentation. So I started going through their todo list to see if it is planned for some point in the future. I didn’t see it on the feature list for 4.1 or for the new feature list for 4.1. Nothing in the todo list for 5.0 about constraints. Ahhhhh, found it. On the todo list for 5.1 is an entry under ‘New functionality’ called ‘Column-level constraints’. I wasn’t able to find any information about when MySQL 5.1 might become the stable version. The only thing I did find was an email where the guess for MySQL 5.1 was sometime around 2006.