Product articles
SQL Change Automation
SQL Code Analysis
The Whys and Wherefores of Untrusted or…

8 January 2019

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.

8 January 2019

The Whys and Wherefores of Untrusted or Disabled Constraints

Having untrusted or disabled FOREIGN KEY or CHECK constraints in your databases will degrade data consistency and integrity and can cause query performance problems. Phil Factor explains how to detect these and other table-related issues, during development, before they cause trouble further down the line.

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.

You may also like