What is a Foreign Key?

Do you trust your foreign keys?

A foreign key is a link between 2 tables that is used to enforce referential integrity in the database.

For example, if you have an order and a customer table, there is probably a logical relationship between the 2 tables. An order can’t exist without being linked to a customer record.

When trusted, these keys help ensure that the data in your database stays ‘clean’ and logical.

By establishing trusted foreign keys in SQL Server between your tables, the optimizer is able to make some assumptions about the data and therefore make more efficient execution plans for your queries.

Foreign keys are trusted by default when created since checks are done to ensure the data all lines up. If the data does not match as expected, errors are relayed and the key is not created. Once established they keys will prevent ‘bad’ data from being entered into your database so long as they remain trusted.

What is an Untrusted Foreign Key?

An untrusted foreign key is one that has had the referential integrity of the relationship removed. SQL Server is unable to ‘trust’ that the data is clean in both tables and therefore isn’t exactly sure of the best way to proceed. This can start to show itself by queries getting slower as the optimizer starts to perform extra checks to make sure the data it is getting is good.
Read the full article here

The post Untrusted Foreign Keys appeared first on .