Database Reference
In-Depth Information
Figure 7-6.
Deleting the row from the referenced table without an index specified on the referencing column
Missing indexes on the referencing columns could have a huge performance impact in the case of large tables.
They would introduce excessive and unnecessary I/O load and contribute to the blocking. Also, besides referential
integrity support, those indexes can be helpful during the join operations between the tables. It is usually a good idea
to create those indexes when we create the foreign keys constraints.
In some cases, foreign key constraints can help the Query Optimizer. It can help eliminate unnecessary
joins, especially when views are involved, as well as improve performance of some queries in Data Warehouse
environments.
■
Note
We will discuss join elimination in greater detail in Chapter 9, “Views.”
Unfortunately, foreign keys are incompatible with some SQL Server features. For example, when a table is
partitioned and referenced by a foreign key, you cannot alter the table and switch the partition to another table. You
can still have the table partitioned, however, if a partition switch is not involved. Another example is table truncation.
You cannot truncate a table when it is referenced by foreign keys.
Defining foreign key constraints is usually a good thing, assuming, of course, that you are OK with the extra
indexes and that the system can handle the slight performance overhead introduced by index seek operations during
referential integrity checks. In OLTP systems, I recommend that you always create the foreign keys referencing catalog
entities where the amount of data is relatively small and static. For example, Order Entry system catalog entities
would include
Articles
,
Customers
,
Warehouses
, and so forth. You need to be careful, however, when dealing with
transactional entities that store billions of rows and handle thousands of inserts per second. I would still use foreign
keys whenever possible, though I would analyze the performance implications on a case-by-case basis.
There are a couple catalog views:
sys.foreign_keys
and
sys.foreign_key_columns
, which provide the
information concerning foreign key constraints defined in the database.
Check Constraints
Check constraints
enforce domain integrity by limiting the values that you can put into the column or multiple
columns in the row. They specify the logical expression that is evaluated every time a row is inserted or when
corresponding columns are modified, and they fail the operation when an expression is evaluated as FALSE.
