Database Reference
In-Depth Information
Figure 7-1. Unique constraints in SQL Server Management Studio
Generally, it is good idea to have uniqueness enforced when data is unique. This helps to keep the data clean and
avoid data integrity issues. Unique constraints can also help Query Optimizer to generate a more efficient execution
plan. The downside is that you will have to maintain another non-clustered index for every uniqueness condition
you define. You need to consider the data modification and index maintenance overhead that they introduce when
choosing to implement constraints.
Whether to choose a unique constraint or unique index largely depends on personal preferences. Uniqueness
usually comes in the form of a business requirement, and enforcing uniqueness with constraints can contribute to
system supportability. On the other hand, unique indexes are more flexible. You can include columns and use those
indexes for query optimization purposes in addition to uniqueness enforcement. You can also specify the sorting
order, which can help in some rare cases.
Like primary key constraints, there is no special catalog view for unique constraints. There is the column
is_unique_constraint in the sys.indexes catalog view, which shows if index is created as a unique constraint.
Foreign Key Constraints
Foreign key constraints identify and enforce relations between tables. Think about our Orders and OrderLineItems
tables example. Every OrderLineItems row belongs to corresponding Orders row and cannot exist by itself. These
kinds of relations are enforced with foreign key constraints.
Like other constraints, foreign keys enforces data integrity. It is always easier to deal with clean and correct data
rather than cleaning up the data on the fly. In addition, during the development and testing stages, foreign keys help
catch a good number of bugs related to incorrect data processing.
However, foreign keys come with their own price. Every time you insert the data into the referencing table,
you need to check to see if there are corresponding rows in the referenced table. Let's take a look at the example
using the same Orders and OrderLineItems tables we created earlier in this chapter. When you insert a row into
OrderLineItems table without any foreign keys defined, the query needs to perform only one clustered index insert
operation, as shown in Figure 7-2 .
Figure 7-2. Inserting a row into the referencing table with no foreign key constraint defined
 
Search WWH ::




Custom Search