8 January 2019
8 January 2019

The Whys and Wherefores of Untrusted or Disabled Constraints

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Having untrusted or disabled FOREIGN KEY 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 ever 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 so some bulk operation, and then aren’t then re-enabled with a ‘check’, after the bulk operation is completed.

During development, you need a way to detect these, and other table-related issues, such as lack of clustered indexes, lack of primary key and so on, before they cause problems further down the line.

Temporarily disabling and re-enabling constraints

If you wish to stock a database with data, you would usually disable all constraints, and then re-enable them with a check that all is well, once all the data is in place. This check, at the point of re-enabling a constraint, verifies that every existing row complies with the constraint’s conditions and throws an error if an inconsistency is found. If all the rows pass the check, the constraint is both enabled and trusted. If you enable a constraint without running the check, it will function, but won’t be trusted, and won’t be considered by the optimizer. The optimizer will use “primary keys, enforced and trusted referential constraints and any other types of constraints in place on the tables and columns that make up the query, which tell the optimizer the limits on possible data stored within the tables referenced.” (see SQL Server Execution Plans, Third Edition, by Grant Fritchey).

This process of disabling and enabling constraints is far more sensible than deleting them and recreating them, mainly because you guarantee to keep everything that was there before without having to save and execute a creation script of all the constraints.

The alternative to disabling constraints, temporarily, is to leave them all enabled, but then you must do the import in strict dependency order. You will also discover that each row is checked as it is inserted. This may take a bit of time with a large table, and it will require schema modification locks. If a table is of any size, and you are inserting into an empty table, it is worth considering disabling constraints for the duration of the insert, and then enabling them again. However, if you are adding rows to an existing table, this strategy forces a check on the entire table, not just the inserted rows, which could turn out to be a slow option.

It isn’t just a bulk load that requires constraints to be temporarily disabled: it can be any complex operation, such as a merge, that is likely to be slowed down because the integrity checks are being made more often than necessary. In these circumstances, it pays to temporarily disable checks, but some middleware will occasionally forget to re-enable these constraints with an initial check, after a complex operation.

To disable a FOREIGN KEY or CHECK constraint in a table you can use the statement

And to enable it again, you can use:

To disable all constraints in a table:

To disable all constraints in a database:

And to re-enable them:

In each case, we use the CONSTRAINT clause to specify whether we wish to enable or disable the specified constraint, or constraints. We disable them by specifying NOCHECK CONSTRAINT and enable them using CHECK CONSTRAINT.

This CHECK CHECK syntax, when enabling constraints, looks more like a stutter than a DLL phrase and could be the cause of some of the mistakes that are made. The first CHECK is the tail of the WITH CHECK statement that specifies that we wish to validate every row of the table against the constraint. The second CHECK is first part of the CHECK CONSTRAINT clause to enable the constraint.

You can also unintentionally mark a constraint as untrusted if you set the constraint as NOT FOR REPLICATION. You cannot then make the constraint trusted by running the CHECK CHECK command, because replication procedures will be able to alter the data without triggering the constraint, so that the presence of the constraint doesn’t ever guarantee consistency. It is like having a muzzled guard-dog.

Testing your constraints

So, let’s test all this out with a very simple example.

Here’s the report from running DBCC CHECKCONSTRAINTS:

The next section investigates and fixes the untrusted constraints:

Looking out for problems

SQL Prompt supports a whole range of static code analysis rules that it will flag up when you are writing or updating SQL code. Also, SQL Change Automation generates a code analysis report informing you of violations of these rules, so you can spot problems in your database builds and releases. However, some of the dynamic code analysis rules, which use a live connection, are not yet supported.

If you have SQL Compare, you can compare your development database to a version in source control that you know is free from these issues, and it will detect the differences. In the following example, the CHECK constraint on MyPostcodeTable is disabled (and so also untrusted) and the FOREIGN KEY on MyReferencingTable is enabled but untrusted.

You can also ferret out these and other table-related issues using the SQL code I provide in my article, SQL Server Table Smells.

Conclusion

FOREIGN KEY and CHECK constraints normally just check altered rows, because a constraint, in normal circumstances, knows that all the other rows have, at some point, been checked.

A disabled constraint doesn’t do any checking, and an enabled one does. If a CHECK or FOREIGN KEY constraint is created, it doesn’t know whether all rows have been checked, so it goes ahead and does the check for all existing rows. However, if you disable a CHECK or FOREIGN KEY constraint, and then later reenable it, it has no idea whether all the existing rows are valid, because you may have slipped in some invalid rows in the interim.

You have a choice of doing a check of all the rows, in which case the constraint is trusted, or of postponing this check, in which case the FOREIGN KEY relationship can’t be trusted. An untrusted constraint will work as normal, but unless a row is altered, it won’t be checked.

There is a good reason for this, to do with bulk import into big tables, but making this behavior the default is a tiresome mistake from a long time ago which can’t easily now be changed without breaking existing code. It is something you need to monitor, because a loss of referential integrity can have scary consequences.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Quickly rename variables all at once with SQL Prompt

    You’re going to run into situations where someone has made poor choices in naming objects, or maybe the business logic has changed and the object name no longer makes sense. Either way, easily and quickly taking care of renaming the objects within scripts can be a pain. Let’s take this code as an example: The

  • Article

    Automating Builds from Source Control for the WideWorldImporters Database

    Sample databases from Microsoft are like YouTube stars: they’re quirky, popular with their fans, and always online somewhere. It makes them a common choice when it comes to creating prototypes, but their lovable quirkiness sometimes makes this tricky. Here, we’ll be prototyping an automated database build from source control, for the WideWorldImporters database. Microsoft uses

  • Event

    SEACON 2018 (The Study of Enterprise Agility Conference)

    SEACON is THE Enterprise Agility conference that brings business and technology together, and Redgate are pleased to be taking part as one of the sponsors. Following the sold out conference in 2017, the 2018 edition will again host FinTech practitioners and Thought Leaders in Enterprise Transformation, Entrepreneurial Leadership, Agile , DevOps, Cloud and Fintech.

  • Article

    Quick SQL Prompt tip – using the ii snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the “ii” snippet that helps with inserting data into a table. If I type “ii” and hit tab, I quickly get the

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly