Check constraints in SQL Server

This goes in the “here’s something I learned today” bucket.

I was working on my ASP.NET based photo gallery sample/experiment and realized that I needed to ensure that the names of images I stored in the database couldn’t contain illegal filename characters. This is because I allow the user to specify a path like http://imgsample/P51.jpg and I use URL rewriting to translate this to http://imgsample/ViewImage.aspx?name=P51 or http://imgsample/ViewImage.aspx?ID=12&size=med.

Not being a database guy (yet!) as I thought about ensuring only valid names I figured I could use form validation. But then I remembered “hey this is a database app”, can’t the database enforce this? A few minutes browsing MSDN clued me into the Check Constraints capability on columns. All I had to do was add the following expression as a check constraint to my images table.

(left([img_name],1) <> ' ' 
and right([img_name],1) <> ' ' 
and charindex('\',ltrim(rtrim([img_name]))) = 0 
and charindex('/',ltrim(rtrim([img_name]))) = 0 
and charindex('?',ltrim(rtrim([img_name]))) = 0 
and charindex('&',ltrim(rtrim([img_name]))) = 0 
and charindex('*',ltrim(rtrim([img_name]))) = 0 
and charindex('..',[img_name]) = 0)

Now whenever I enter an invalid name I get a SQL error on the insert. Cool. Next I need to figure out how to handle those errors gracefully.

© Charlie Kindel. All Rights Reserved.

1 comment

Comment on this post

This site uses Akismet to reduce spam. Learn how your comment data is processed.