Professional SQL Server™ 2005 Programming

Chapter 5. Reviewing Keys and Constraints

You've heard me talk about them, but now it's time for a serious review of keys and constraints. SQL Server has had many changes in this area over the last few versions, and that trend has continued with SQL Server 2005.

We've talked a couple of times already about what constraints are, but let's review in case you decided to skip straight to this chapter.

NOTE

A constraint is a restriction. Placed at either column or table level, a constraint ensures that your data meets certain data integrity rules.

This gets back to the notion that I talked about in Chapter 1, where ensuring data integrity is not the responsibility of the programs that use your database, but rather the responsibility of the database. If you think about it, this is really cool. Data is inserted, updated, and deleted from the database by many sources. Even in standalone applications (situations where only one program accesses the database), the same table may be accessed from many different places in the program. It doesn't stop there though. Your database administrator (that might mean you if you're a dual role kind of person) may be altering data occasionally to deal with problems that arise. In more complex scenarios, hundreds of different access paths can exist for altering just one piece of data, let alone your entire database.

Moving the responsibility for data integrity into the database itself has been revolutionary to database management. There are still many ...