Chapter 11. Indexes and Constraints on Tables

In previous chapters, we concentrated primarily on retrieving information from existing tables. This chapter revisits the creation of tables, but focuses on how indexes and constraints can be added to tables to make the tables more efficient and to increase the integrity of the data in the tables (and hence in the database). Referential integrity constraints and other constraints are also discussed.

SQL Server does not need indexes to successfully retrieve results for a SELECT statement. But, an index may speed up queries and searches on the indexed columns and may facilitate sorting and grouping operations. As tables get larger, the value of using proper indexes becomes more of an issue. Indexes can be used to find data quickly that satisfy conditions in a WHERE clause, find matching rows in a JOIN clause, or to efficiently maintain uniqueness of the key columns during INSERTs and UPDATEs.

Constraints are a very powerful ways to increase the data integrity in a database. Integrity implies believability and correctness. Any data that destroys the sense of correctness is said to lack integrity. For example, a constraint is used to establish relationships with other tables. A violation of integrity would be, for instance, if a nonexistent referenced row were included in the relationship. The CONSTRAINT clause can be used with the CREATE TABLE and the ALTER TABLE statements to create constraints or delete constraints, respectively.