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.