Step 5: Use constraints and relationships to keep out bad data |
 |
By Serdar Yegulalp, Contributor
13 May 2005 | SearchSQLServer.com |
|
|
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.
ENSURING DATA INTEGRITY IN SQL SERVER
 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
| ABOUT THE AUTHOR: |
|
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
|
|
|
|
|
|
