In the previous blog, we discussed the Foreign Keys Constraints and how the CHECK Constraints are useful to verify the data that goes into your tables. But, sometimes when you need to load a lot of data quickly, SQL Server allows you to temporarily disable any CHECK or FOREIGN KEY constraint in order to improve performance.

Once the data load finishes, enable the constraints again, and SQL Server will check them behind the scenes. This process will ensure that the data load activity didn’t violate those constraints. So, you can use the following T-SQL scripts to enable and disable the constraints.

ALTER TABLE <SchemaName>.<TableName>  WITH { CHECK | NOCHECK } CHECK CONSTRAINT <ConstraintName >
ALTER TABLE <SchemaName>.<TableName>  NOCHECK CONSTRAINT <ConstraintName>

If you follow the above script, the CONSTRAINTS can be used by specifying “with check” or “with nocheck”.

WITH CHECK CONSTRAINTS: it tells to SQL Server the user wants it to validate the constraint against every single row in the table. If the validation is successful, enable it.

WITH NOCHECK CONSTRAINTS: It is the default setting for an existing constraint. It means that the constraint is enabled, but no validation has been made on it. Fundamentally, this will allow you to have child records without a corresponding parent. SQL Server doesn’t TRUST the constraint as it has not validated it. We refer to such foreign key as an “Untrusted Foreign Key”.

But the problem arises when you forget to enable them correctly….. Human error can occur!

Finding Untrusted Foreign Keys and Constraints

Here is the query which will help you find all the Untrusted Foreign Keys and Constraints in one shot for all the DBs.

CREATE TABLE #constraints_detaills (dbname NVARCHAR (100), 
untrusted_keyname NVARCHAR (1000), 
constraints_type NVARCHAR (500)
)
CREATE TABLE #temp (
id INT IDENTITY(1,1),
dbname NVARCHAR (100),
status_fk int,
status_cc int
)
INSERT INTO #temp
SELECT name,0,0 FROM sys.databases WHERE database_id not in (1,2,3,4,32727)
SET NOCOUNT ON
DECLARE @id INT
DECLARE @dbname NVARCHAR (100)
DECLARE @sql_fk VARCHAR (MAX)
DECLARE @sql_cc VARCHAR (MAX)
WHILE EXISTS(SELECT TOP 1 dbname FROM #temp WHERE status_fk = 0 AND status_cc = 0)
BEGIN
SELECT @id= id, @dbname = dbname
FROM #temp  WHERE status_fk = 0 AND status_cc = 0
SET @sql_fk = 'USE '+@dbname +' SELECT '''+@dbname +''',
  ''['' + s.name + ''].['' + o.name + ''].['' + i.name + '']'' AS keyname, 
  ''Foreign Keys''
  FROM sys.foreign_keys i
  INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;'
SET @sql_cc = 'USE '+@dbname + ' SELECT '''+@dbname +''', 
  ''['' + s.name + ''].['' + o.name + ''].['' + i.name + '']'' AS keyname, 
  ''Check Constraints''
  FROM sys.check_constraints i
  INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
              WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;'
INSERT INTO #constraints_detaills
EXEC (@sql_fk)
INSERT INTO #constraints_detaills
EXEC (@sql_cc)
                                   
UPDATE #temp
SET status_fk = 1, status_cc = 1
WHERE id = @id AND status_fk = 0 AND status_cc = 0
END
SELECT * FROM #constraints_detaills
DROP TABLE #temp
DROP TABLE #constraints_detaills

 Keep in mind the below points when you are enabling and disabling the CONSTRAINTS;

• When you enable the CONSTRAINTS, the validation process of the existing data can take a lot of resources like CPU, IO, and Memory. Therefore, It is recommended to do it during maintenance windows, if the tables are pretty big. 
• You may see improved query performance for the tables with trusted constraints. 

If you find it useful, please do share your experience by commenting below.

The post Finding Untrusted Foreign Keys and Constraints appeared first on .