Check Constraints in SQL Server 2000

by William Ryan

These days, Relational Database Management Systems are very powerful.  They do so much for you that really knowing a database like Sql Server or Oracle is quite an accomplishment.  Anyway, as a programmer, the more you know the better off you are.

Let's say that you have an application and you don't want to allow negative values in your database.  If even one gets in, it could cause problems.  You have three options, client side validation, server side validation or both.  Let's examine what happens in all three.


Client Side Validation:

You write bullet proof code that doesn't allow negative values in any textbox that maps to the column in question.  A few things could go wrong.  The first is that you or another programmer could make a mistake in the future and allow negatives to enter your database.  The second is that you or another programmer could add a new form and forget to include the validation code.  The third is that someone could write a query outside of your app and run it, which enters negative numbers....or someone makes a typo in Enterprise manager or similar tool.

Server Side Validation:

Provided you write your constraint correctly, something that's easily tested, non-conforming data can never enter your database, not from an Application, Query Analyzer, or Enterprise Manager.

Mixed Validation:

This approach takes the most work, but it is probably the preferred method for most instances.  But the Server Side method can guarantee that I never get bad data in the database, so why would I want to write extra code?  User Friendliness and efficiency.  Users don't want to enter data and hit a button hoping they did it right, only to have the database come back and yell at them.  Moreover, why not try to filter as much junk as you can and then use the Server as final watchdog?

Enough already, let me show you how to do it.  Let's say we have a table named Employees and one of the business rules is the following:  "Every employee can no less than 0 dependants, and no more than 15.  While they may physically have more than 15, our company will not recognize any over the 15 ceiling"

CREATE TABLE Employees(        EmployeeId     INT       ,AGE            INT       ,FirstName      VARCHAR(75)       ,LastName       VARCHAR(75)       ,Dependents     INT       )

Now, we have virtually no validation at the database level.  At this point, we'd have to do something like trap the Validating event of every control in our UI and ensure that validation fails if the code is non-conforming.  Like I mentioned above, your data's integrity could be comprimised by a programmer firing a query and making a mistake or adding a new visual entry point to the field in your database.  Nothing I can do with Validating events can quell this risk becuase your app isn't the only way people access the data.  However, let's shell out a basic example:

Private Sub SomeTextBox_OnValidating(ByVal sender As System.Object, ByVal e As CancelEventArgs) Handles                SomeTextBox.Validating If CType(SomeTextBox.Text, Integer) < 0 Or CType(SomeTextBox.Text, Integer) > 15 Then          e.Cancel = False End If End Sub

(I know, only an idiot would use a TextBox for this type of field, but that doesn't invalidate the point I'm trying to convey)

So, lets assume that you wrote the validating code already.  But you knew that someone in Finance would probably find a way to screw things up....

Modifying the above DDL to the following, coupled with the Validating Event code, would stop anyone from compromising the integrity of your data and preempt any data entry error that may be made within the context of your UI.

CREATE TABLE Employees(        EmployeeId     INT       ,AGE            INT       ,FirstName      VARCHAR(75)       ,LastName       VARCHAR(75)       ,Dependents     INT          CHECK( Dependents > 0 AND Dependents <= 15))

Summary:

I have a pretty strong bias when it comes to server side validation and I don't hide it.  As a programmer and someone that's all too human, I've tried writing code that was 'bullet proof' only to find out that it was far from it because someone did something I never anticipated.  I can just about assure you that if you think you can 'code around' every potential problem, you have a lot of disappointment waiting for you.  Bullet proof code doesn't mean that it works and takes into account most scenarios.  It means doing everything at your disposal to prevent unintended issues.  Everything!  And it often takes a little more work, but from what I've seen, doing it right is always less work than doing it over.

See Also:
Transact Sql Overview|Check Constraints|Data Integrity