More powerful database constraints with regular expressions

Is it just me or do you agree that we should be able to easily apply regular expression constraints to a column in a table without writing a stored procedure? For example if I want to guarantee that a varchar only contains alpha numeric characters with no spaces in most databases I currently have to write a stored procedure and use a trigger to call it on insert and update. Too much work for something that should be so simple!

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.

Sure, with Active Record in Ruby I can easily do this in code or in Java using the Hibernate validator but it’s a different story when you’ve got a database with multiple legacy applications and data loading systems running against it. In those cases the database if the final line of defense!

This is just a rant but I’m shocked that this situation hasn’t markedly improved over the past 10 years!

This entry was posted in Database, Software Engineering, Systems Administration. Bookmark the permalink.