News Stay informed about the latest enterprise technology news and product updates.

Step 5: Use constraints and relationships to keep out bad data

Learn why you must use SQL Server's CHECK constraints to enforce constraints on inserted data.

This is one point I can't stress strongly enough. Almost every database solution has some method for enforcing constraints on inserted data. SQL Server has this in the form of CHECK constraints. Use them.

Why? One rule of database design that comes up often: "Databases should protect themselves." In other words, don't trust the client to always pass valid data -- i.e., to ensure whether or not a value for a column is within gamut or doesn't break referential integrity. Applications can't break rules set up in the database itself.

Even if you only intend for the database to be used with one front end, it's always possible for someone with administrative access to go right to the database console and punch things in manually, thereby defeating any protections against bad data built into the front end. (And consider this: What happens if you leave, and the database you created is plugged into a whole new front end?)

When creating constraints, they should follow some of the same rules that you have for triggers: For one, keep them relatively simple and atomic so they can be debugged easily. The performance overhead for constraints should not even be an issue, and what you gain back in the long run in terms of data integrity is priceless.


 Home: Introduction
 Step 1: Back up, optimize and enable safety features
 Step 2: Segregate data aggressively into files and filegroups
 Step 3: Consider using implicit transactions
 Step 4: Be careful how you enforce internal referential integrity through triggers
 Step 5: Use constraints and relationships to keep out bad data
 Step 6: Don't expose interfaces that create dynamic SQL to the end user
 Step 7: Use a "check-in/check-out" mechanism for contested data

Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.