Chapter 6. Imposing Constraints and Relationships

As you work with SQL Server, you'll often want to control the contents of your database to ensure the quality of the data it contains. For example, you might want to ensure that your customers database doesn't contain two duplicate records for the same customer. Similarly, you wouldn't want an orders table in that database to contain an order for an item that doesn't exist in your catalog. Both of these situations could cause embarrassing situations for your business and possibly have a negative impact on your organization's profitability.

Database administrators refer to this type of quality assurance as ensuring the integrity of the database. Microsoft SQL Server provides a number of mechanisms for enforcing database integrity. Collectively, these mechanisms are known as constraints, and this chapter shows you how to effectively use them to control the contents of your databases.

Introducing Constraints

SQL Server 2008 supports five different types of database integrity constraints. Two of them primarily enforce business rules imposed upon the database, whereas the other three ensure the integrity of database row uniqueness and relationships.

The two types of constraints that primarily serve to enforce business logic in your databases are the following:

  • DEFAULT constraints supply values to fill fields when the user doesn't provide a value.

  • CHECK constraints limit the values that users may insert into a particular database field.

The three ...

Get Microsoft® SQL Server® 2008 For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.