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 .