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.
May 26th, 2004 at 7:30 am Jeff Eckermann
As of version 7.4, the regular expression engine used by PostgreSQL is the same as that used by tcl (the language, that is). I haven’t investigated it’s capabilities yet, but I am given to understand that it supports a lot of Perl-style regex extensions. Whether it is “PCRE”, I can’t say.
July 12th, 2004 at 3:16 am Neil Conway
Jeff: Yeah, 7.4+ uses an import of the regex code in recent versions of TCL. That code is itself a new version of Henry Spencer’s regex code (updated by none other than Henry Spencer himself); it is not pcre, though. Nevertheless, PostgreSQL’s regex implementation is pretty featureful.
June 19th, 2005 at 8:49 pm Preston
Thanks for the example! One small correction, I think your last line should read:
( first_name ~ ‘^[a-z]+$’)
January 26th, 2006 at 9:01 am Todd Huss’ blog » Blog Archive » More powerful database constraints with regular expressions
[...] Postgres is one of the few databases I’m aware of that currently solves the problem. You can see an example at Joseph Scott’s Blog of using a regex in a check constraint. [...]
February 6th, 2006 at 11:40 am Rhyll > PostgreSQL Blog > Using Regular Expressions in PostgreSQL
[...] One a related note, I wrote a simple example on using regular expressions in check constraints. [...]
September 11th, 2006 at 3:52 am Peter
No Preston, The star means the expression is case insensitive.
September 16th, 2006 at 1:18 pm Preston
Hi Peter,
If the purpose of this check is to only allow lowercase letters (as it is stated in the description), then what we want is a case sensitive search. With the star, the check will allow both uppercase and lowercase letters.
June 16th, 2008 at 4:40 am jayanth
syntax:
ALTER Table Table_name
ADD constraints CHECK_column_name
CHECK (REGEXP_LIKE(column_name,’(([0-9][a-z]{1}|[a-z][0-9]{1}))’));
consider the above syntax whether it is an apporiate syntax to create an constraint for a particular column
Example:
ALTER Table userinfo
ADD constraints CHECK_password
CHECK (REGEXP_LIKE(password,’(([0-9][a-z]{1}|[a-z][0-9]{1}))’));
unable to determine the fault ,since the query is unable to get updated..
Kindly reply at your earliest.
Regards
Jayanth S.