The Whys and Wherefores of Untrusted or Disabled Constraints
The Whys and Wherefores of Untrusted or Disabled Constraints
Explore the whys and wherefores of untrusted or disabled constraints.
Join the DZone community and get the full member experience.Join For Free
Having untrusted or disabled
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.
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
CHECK constraint in a table you can use the statement
ALTER TABLE <TableName> NOCHECK CONSTRAINT <ConstraintName>
And to enable it again, you can use:
ALTER TABLE <TableName>WITH CHECK CHECK CONSTRAINT <ConstraintName>
To disable all constraints in a table:
ALTER TABLE <TableName> NOCHECK CONSTRAINT ALL
To disable all constraints in a database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
And to re-enable them:
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
NOCHECKCONSTRAINT and enable them using
CHECKCHECK 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
WITHCHECK statement that specifies that we wish to validate every row of the table against the constraint. The second
CHECK is first part of the
CHECKCONSTRAINT clause to enable the constraint.
You can also unintentionally mark a constraint as untrusted if you set the constraint as
NOTFORREPLICATION. You cannot then make the constraint trusted by running the
CHECKCHECK 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.
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
The next section investigates and fixes the untrusted constraints:
/* 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 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
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.
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
FOREIGNKEY 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
FOREIGNKEY 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 if postponing this check, in which case the
FOREIGNKEY 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.
Published at DZone with permission of Phil Factor . See the original article here.
Opinions expressed by DZone contributors are their own.