Chapter 14. Implementing Data Integrity

by Paul Jensen

IN THIS CHAPTER

• Types of Data Integrity

• Enforcing Data Integrity

• Constraints

• Rules

• Defaults

Ensuring the integrity of data is one of the most important tasks of an administrator. Key business decisions are often made based on information in the database; if the data is misrepresented, incorrect conclusions might be drawn. Consider a car manufacturer who uses a product code R01 (R zero one) to represent every red car sold. To predict next year's sales of red cars, they run a query on the database to count the instances of R01. They determine that they sold far fewer red cars than expected, and reduce production. Halfway through the year, they run out of red cars. On closer inspection of the data, they determine that they had actually sold plenty of red cars , but the data entry personnel had in many cases incorrectly entered the code as Ro1 (R oh one), R0l (R zero L) or Rol (R oh L). So does the blame fall on the data entry personnel? Not likely. Integrity constraints to prevent incorrect entries are the proper solution. When it comes to data integrity, the buck stops at the administrator's desk.

This chapter focuses on enforcing integrity through the use of constraints, rules, and defaults. Data integrity can also be enforced at the application level, which is a subject for a book on application design, and through stored procedures and triggers. Because stored procedures and triggers have additional functionality outside of data integrity, they are covered in separate chapters of their own. Check out Chapter 28, "Creating and Managing Stored Procedures in SQL Server," and Chapter 29, "Creating and Managing Triggers" to learn more.