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.
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
|
1 |
ALTER TABLE <TableName> NOCHECK CONSTRAINT <ConstraintName> |
And to enable it again, you can use:
|
1 |
ALTER TABLE <TableName>WITH CHECK CHECK CONSTRAINT <ConstraintName> |
To disable all constraints in a table:
|
1 |
ALTER TABLE <TableName> NOCHECK CONSTRAINT ALL |
To disable all constraints in a database:
|
1 |
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' |
And to re-enable them:
|
1 |
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
IF Object_Id('dbo.MyPostcodeTable') IS NOT NULL DROP TABLE dbo.MyPostcodeTable IF Object_Id('dbo.MyReferencingTable') IS NOT NULL DROP TABLE dbo.MyReferencingTable; IF Object_Id('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable; -- firstly create the referenced table CREATE TABLE dbo.MyTable (MyUniqueColumn INT CONSTRAINT MyPrimaryKey PRIMARY KEY); --now create the referencing table CREATE TABLE dbo.MyReferencingTable (MyForeignKeyColumn INT CONSTRAINT MyFKConstraint REFERENCES MyTable); --insert some primary keys into our referenced table INSERT INTO dbo.MyTable VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); --we'll disable the FOREIGN KEY constraint ALTER TABLE dbo.MyReferencingTable NOCHECK CONSTRAINT MyFKConstraint; --can we now add values that aren't valid references? INSERT INTO dbo.MyReferencingTable VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19),(20); /* this is all fine. Because the constraint is disabled, we can put what we like in the way of integer values into the referencing column */ -- we now enable the constraint, but without checking the data is valid ALTER TABLE dbo.MyReferencingTable CHECK CONSTRAINT MyFKConstraint; /* now we enable the constraint. There could be trouble ahead, but what if we put a kosher value in? Will it spot all those other dodgy values? */ INSERT INTO dbo.MyReferencingTable(MyForeignKeyColumn) VALUES(1); /* no problem with doing this because it is valid, even though there are several faulty references already there. We conclude that only altered rows (inserted, updated) are checked and everything else isn't. Let's try inserting a bad value with no corresponding PK */ INSERT INTO dbo.MyReferencingTable(MyForeignKeyColumn) VALUES(21); /* BANG!! Msg 547, Level 16, State 0, Line 9 The INSERT statement conflicted with the FOREIGN KEY constraint "MyFKConstraint". The conflict occurred in database "PhilFactor", table "dbo.MyTable", column 'MyUniqueColumn'. The statement has been terminated. */ --now create a table containing postcodes CREATE TABLE dbo.MyPostcodeTable ( postcode varchar(15) CONSTRAINT for_Valid_Postcode CHECK ( CASE WHEN postcode LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' OR postcode LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' OR postcode LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' THEN 1 ELSE 0 END=1) ) INSERT INTO dbo.MyPostcodeTable(postcode) SELECT postcode FROM (VALUES ('SK1 3AU'),('BT7 3GQ'),('NR1 3SR'),('SE9 5LB'),('PO4 0PX'),('BT78 5LU'), ('W1F 7JR'),('B46 2QP'),('BL8 4AA'),('NR2 4AZ'))postcodes(postcode) /* all of these are valid and they went in well */ --we'll disable the CHECK constraint ALTER TABLE dbo.MyPostcodeTable NOCHECK CONSTRAINT for_Valid_Postcode; INSERT INTO dbo.MyPostcodeTable(postcode) SELECT postcode FROM (VALUES ('DAVE'),('DEE'),('DOZY'),('BEAKY'),('MICK'),('TITCH'))postcodes(postcode) /* Those aren't valid but they went in!!! */ ALTER TABLE dbo.MyPostcodeTable CHECK CONSTRAINT for_Valid_Postcode; --- we now have bad postcodes in that table, but the constraint is enabled /* We can actually get a report of all the broken relationships and bad data if we want it */ DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS; |
Here’s the report from running DBCC CHECKCONSTRAINTS:
The next section investigates and fixes the untrusted constraints:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
/* now let's see if our FOREIGN KEY constraint is trusted */ SELECT QuoteName(Object_Schema_Name(parent_object_id)) + '.' + QuoteName(Object_Name(parent_object_id)) + '.' + QuoteName(name) FROM sys.foreign_keys AS i WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0; -- it is enabled but not trusted /* There it is, listed as being untrusted [dbo].[MyReferencingTable].[MyFKConstraint] now let's see if our CHECK constraint is trusted */ SELECT QuoteName(Object_Schema_Name(parent_object_id)) + '.' + QuoteName(Object_Name(parent_object_id)) + '.' + QuoteName(name) FROM sys.check_constraints AS CC WHERE CC.is_not_trusted = 1 AND CC.is_not_for_replication = 0 AND CC.is_disabled = 0; /*it is enabled but not trusted [dbo].[MyPostcodeTable].[for_Valid_Postcode] We want that foreign key constraint trusted!! So, all we need to do is to execute this code.... */ ALTER TABLE dbo.MyReferencingTable WITH CHECK CHECK CONSTRAINT [MyFKConstraint]; /* Aargh! Msg 547, Level 16, State 0, Line 39 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyFKConstraint". The conflict occurred in database "PhilFactor", table "dbo.MyTable", column 'MyUniqueColumn'. */ /* incidentally, you can generate these statements just for your untrusted foreign keys with this code */ SELECT 'ALTER TABLE ' + QuoteName(Object_Schema_Name(parent_object_id)) + '.' + QuoteName(Object_Name(parent_object_id)) + ' WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname FROM sys.foreign_keys AS i WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0; /* now we have to clean up our bad references. We'll just reset all the bad references to NULL */ UPDATE dbo.MyReferencingTable SET MyForeignKeyColumn = NULL FROM dbo.MyReferencingTable LEFT OUTER JOIN dbo.MyTable ON MyReferencingTable.MyForeignKeyColumn = MyTable.MyUniqueColumn WHERE MyUniqueColumn IS NULL; /* now retry enabling the FOREIGN KEY constraint with checks*/ ALTER TABLE dbo.MyReferencingTable WITH CHECK CHECK CONSTRAINT [MyFKConstraint]; /* now let's see if our FOREIGN KEY constraint is trusted */ SELECT QuoteName(Object_Schema_Name(parent_object_id)) + '.' + QuoteName(Object_Name(parent_object_id)) + '.' + QuoteName(name) FROM sys.foreign_keys AS i WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0; /* Yes!*/ --Now to fix that postcode table DELETE FROM dbo.MyPostcodeTable WHERE CASE WHEN postcode LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' OR postcode LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' OR postcode LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]' THEN 1 ELSE 0 END=0 ALTER TABLE dbo.MyPostcodeTable WITH CHECK CHECK CONSTRAINT [for_Valid_Postcode]; --all done! /* now tear down and mop up */ IF Object_Id('dbo.MyPostcodeTable') IS NOT NULL DROP TABLE dbo.MyPostcodeTable IF Object_Id('dbo.MyReferencingTable') IS NOT NULL DROP TABLE dbo.MyReferencingTable; IF Object_Id('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable; |
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.
Tools in this post
You may also like
-
Article
Problems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)
Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is "tiny bumfit"; I think the tiny bumfit bus goes from Penrith to Carlisle.
-
Article
Creating and Maintaining a SQL Code Snippet Library
How to save all your different types of SQL code snippets in a central library of JSON Snippet Collections, where you can search and edit each collection and then write them out as individual code snippets, for use in SQL Prompt, SSMS or your programmer's clipboard.
-
Article
Quick SQL Prompt tip - restore your tabs, even if you closed them by mistake
It’s sometimes too easy to close SQL Server Management Studio (SSMS) without saving your work. It’s also easy to get rid of tabs and scripts that you later wish you had back. SQL Prompt can help with both these problems. Take a look at the Options for SQL Prompt and you’ll find a set of
-
Article
The Importance of Access Checks and Controls in Database Development
This article illustrates the importance in any database development project of a 'multi-layered' approach to database access controls and security checks, based on the Principle of Least Privilege. It describes a security incident that was raised when databases related to a local development project were found to have been erroneously deployed to a remote test server. It explains the nature of the incident, what went wrong that allowed it to happen, and the measures that were put in place to prevent its recurrence.
-
