Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
Indexes and foreign keys are great tools when confronted with large databases. They can be the answer to a good design and great performance. In this article, I will go through some tips that helped me understand how to use these tools efficiently and streamline my work with complex databases.
Every image example was done with DbSchema. I enjoy this tool because it is diagram oriented, integrates many features, and has a very good price.
Having untrusted or disabled FOREIGNKEY or CHECK constraints isn’t nearly as bad a fault as not having defined any in the first place. However, it does cause issues with data consistency and integrity since you can’t be certain that every row of data complies with the conditions of the constraint. Moreover, neither can the SQL Server query optimizer, which will only consider those constraints marked as ‘trusted’ to help it in determining the best execution plan.
It is rare to find these constraints ‘untrusted,’ but it does happen occasionally, usually after constraints have been disabled temporarily in order to do some bulk operation and then aren’t re-enabled with a ‘check’ after the bulk operation is completed.