Long story short, was reviewing a query and wanted to be sure that Foreign Keys are properly setup between two tables that are used in hundreds of Store Procedures.
The Foreign Key relationship was not defined, but more troubling upon creating it, using the script generated from an existing Foreign Key as a template, noticed that I could not enable\validate it as it was created with the ominous “Not for replication” option.
In this post, will cover how to turn back the effect of this mistake.
Identify Untrusted Objects
Identify Untrusted Foreign Keys
The code below lists untrusted Foreign Keys that have not being explicitly disabled.
SELECT [parentObject] = object_schema_name(tblFK.parent_object_id) + '.' + object_name(tblFK.parent_object_id) , [referencedObject] = object_schema_name(tblFK.referenced_object_id) + '.' + object_name(tblFK.referenced_object_id) , [constraint] = tblFK.name , [isNotForReplicationFlag] = tblFk.is_not_for_replication , [isNotForReplicationLiteral] = case tblFk.is_not_for_replication when 1 then 'Yes' else 'No' end FROM sys.foreign_keys tblFK WHERE tblFK.is_not_trusted = 1 and tblFK.is_disabled = 0 order by [parentObject] , [referencedObject]
Identify Untrusted Objects ( generic )
Identify untrusted objects
SELECT [constraint] =tblO.[name] , [parent] = object_schema_name(tblO.parent_object_id) + '.' + object_name(tblO.parent_object_id) , tblO.[type_desc] , [isNotTrusted] = OBJECTPROPERTY(tblO.object_id,'CnstIsNotTrusted') FROM sys.objects tblO where OBJECTPROPERTY(tblO.object_id,'CnstIsNotTrusted') = 1
Before enabling, we should probably check existing data
DBCC CheckConstraints – Table
DBCC CHECKCONSTRAINTS ('table-name');
DBCC CHECKCONSTRAINTS ('[dbo].[StudentCalendar]');
If there are records in the Parent table, that are missing in the referenced table, they will be listed.
DBCC CheckConstraints – Foreign Key
As one would expect, it is cheaper to target a specific constraint rather than all constraints on the table.
DBCC CHECKCONSTRAINTS ('foreign-key');
DBCC CHECKCONSTRAINTS ('[dbo].[FK_InstructorGroupProducts_ProductCatalog]');
If the constraint has not yet being added, or you want to list the entire dataset that does not match, I will suggest that you go old school to compare the data.
Here are some of those methods:
- Left Outer Join
- Except ( Reference )
Left Outer Join
-- using left outer join select tblADS.[id] , tblADS.[dealid] from [dbo].AssociateDealsSchools tblADS left outer join dbo.AssociateDeals tblAD on tblADS.[id] = tblAD.[id] and tblADS.[dealid] = tblAD.[dealid] where tblAD.[id] is null go
-- using except select tblADS.[id] , tblADS.[dealid] from [dbo].AssociateDealsSchools tblADS except select tblAD.[id] , tblAD.[dealid] from dbo.AssociateDeals tblAD
The query plan for the available pathways is similar.
Altering Existing Constraint
In cases where “is not for replication” is off, we can enable by altering the existing constraint.
ALTER TABLE parent-table with CHECK CHECK CONSTRAINT [constraint-name]
ALTER TABLE [dbo].[StudentCalendar] with CHECK CHECK CONSTRAINT [FK_StudentCalendar_Student]
Recreating Constraint – Foreign Key
In cases where the existing foreign key can not be made good by simply running with “check, check constraint“, we have to drop the constraint and re-create & apply it.
And, so what we need to do :
- Capture the Foreign Key Constraint Creation Step
- Drop the Foreign Key
- Review the captured SQL and remove “Not for replication” if it be there
- Apply the edited SQL
- Issue the “with Check, Check” construct
Script – Stored Procedure
Here is a Stored Procedure that scripts the steps outlined above.
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO if object_id('[dbo].[sp_RecreateConstraintForeignKey]') is null begin exec('create procedure [dbo].sp_RecreateConstraintForeignKey as select 1/0 as [shell] ') end go ALTER procedure [dbo].[sp_RecreateConstraintForeignKey] ( @parent sysname = null , @referenced sysname = null , @currentIsDisabled bit = 0 , @currentIsNotTrusted bit = 0 , @currentIsNotForReplication bit = 0 , @parentRowCountLessThan bigint = 0 , @referencedRowCountLessThan bigint = 0 , @outputFormat tinyint = 1 ) as /* @outputFormat a) 1 - Grid b) 2 - Text */ declare @CHAR_NEWLINE varchar(30) declare @CHAR_BATCHES varchar(30) declare @tblCache TABLE ( [id] int not null identity(1,1) , [constraint] sysname , [parentSchema] sysname , [parent] sysname , [reference] sysname , [dropClause] nvarchar(4000) , [createClause] nvarchar(4000) , [enableClause] nvarchar(4000) , [checkClause] nvarchar(4000) , [rowCountParent] bigint , [rowCountReference] bigint ) set @CHAR_NEWLINE = char(13) + char(10); set @CHAR_BATCHES= char(13) + char(10) + 'go'; ;with cteFK ( objectID , [constraint] , [parentSchema] , [parent] , [reference] ) as ( select [objectID] = tblFK.object_id , [constraint] = quoteName(tblFK.name) , [parentSchema] = quoteName(object_schema_name(tblFK.parent_object_id)) , [parentObject] = quoteName(object_schema_name(tblFK.parent_object_id)) + '.' + quoteName(object_name(tblFK.parent_object_id)) , [referencedObject] = quotename(object_schema_name(tblFK.referenced_object_id)) + '.' + quoteName(object_name(tblFK.referenced_object_id)) FROM sys.foreign_keys tblFK ) , [cteColumns] ( objectID , [parentColumnNames] , [referenceColumnNames] ) as ( select tblFK.object_id , [parentColumns] = Stuff ( ( Select ', ' + tblSC.[name] from sys.foreign_key_columns tblFKC inner join sys.columns tblSC on tblFKC.parent_object_id = tblSC.object_id and tblFKC.parent_column_id = tblSC.column_id where tblFK.object_id = tblFKC.constraint_object_id order by tblFKC.constraint_column_id For Xml Path('') ) , 1 , 2 , '' ) , [referencedColumns] = Stuff ( ( Select ', ' + tblSC.[name] from sys.foreign_key_columns tblFKC inner join sys.columns tblSC on tblFKC.referenced_object_id = tblSC.object_id and tblFKC.referenced_column_id = tblSC.column_id where tblFK.object_id = tblFKC.constraint_object_id order by tblFKC.constraint_column_id For Xml Path('') ) , 1 , 2 , '' ) FROM sys.foreign_keys tblFK ) , [cteRowCount] ( [objectID] , [rowcount] ) as ( select object_ID , [rows] = sum(rows) from sys.partitions tblSP /* Only consider Heaps (0) or the Clustered Index (1) as DMV contains row for each index defined on the table */ where tblSP.index_id in (0, 1) group by object_ID ) insert into @tblCache ( [constraint] , [parentSchema] , [parent] , [reference] , [dropClause] , [createClause] , [enableClause] , [checkClause] , [rowCountParent] , [rowCountReference] ) select [constraint] = tblFK.name , [parentSchema] = cteFK.[parentSchema] , [parent] = cteFK.[parent] , [reference] = cteFK.[reference] , [dropClause] = ' ALTER TABLE ' + [parent] + ' DROP CONSTRAINT ' + [constraint] + '; ' , [createClause] = ' ALTER TABLE ' + cteFK.[parent] + ' WITH NOCHECK ' + ' ADD CONSTRAINT ' + cteFK.[constraint] + ' ' + ' FOREIGN KEY ' + ' (' + cteCol.[parentColumnNames] + ' )' + ' REFERENCES ' + cteFK.[reference] + ' (' + cteCol.[referenceColumnNames] + ' )' + '; ' , [enableClause] = ' ALTER TABLE ' + cteFK.[parent] + ' WITH CHECK CHECK ' + ' CONSTRAINT ' + cteFK.[constraint] + '; ' , [checkClause] = ' DBCC CHECKCONSTRAINTS ' + ' (' + '''' + [parentSchema] + '.' + [constraint] + '''' + ' )' + '; ' , [rowCountParent] = cteRCP.[rowCount] , [rowCountReferenc] = cteRCR.[rowCount] FROM sys.foreign_keys tblFK inner join cteFK on tblFK.object_id = cteFK.objectID inner join cteColumns cteCol on tblFK.object_id = cteCol.objectID inner join cteRowCount cteRCP on tblFK.parent_object_id = cteRCP.objectID inner join cteRowCount cteRCR on tblFK.referenced_object_id = cteRCR.objectID where tblFK.parent_object_id = case when (@parent is null) then tblFK.parent_object_id else object_id(@parent) end and tblFK.referenced_object_id = case when (@referenced is null) then tblFK.referenced_object_id else object_id(@referenced) end AND tblFK.is_not_trusted = isNull ( @currentIsNotTrusted , tblFK.is_not_trusted ) AND tblFK.is_disabled = isNull ( @currentIsDisabled , tblFK.is_disabled ) AND tblFK.is_not_for_replication = isNull( @currentIsNotForReplication , tblFK.is_not_for_replication ) and cteRCP.[rowCount] < case when @parentRowCountLessThan is null then 1E32 when (@parentRowCountLessThan = 0) then 1E32 else @parentRowCountLessThan end and cteRCR.[rowCount] < case when (@referencedRowCountLessThan is null) then 1E32 when (@referencedRowCountLessThan = 0) then 1E32 else @referencedRowCountLessThan end order by cteFK.[parent] , cteFK.[constraint] /* If Display as a Grid */ if (@outputFormat =1) begin select [constraint] , [parent] , [reference] , [dropClause] , [createClause] , [enableClause] , [checkClause] , [rowCountParent] , [rowCountReference] from @tblCache end /* If Display as a Text */ else if (@outputFormat =2) begin select [--sql] = [dropClause] + @CHAR_NEWLINE + [createClause] + @CHAR_NEWLINE + [enableClause] + @CHAR_BATCHES from @tblCache end go exec sys.sp_MS_marksystemobject '[dbo].sp_RecreateConstraintForeignKey' go
declare @parent sysname -- = null , @referenced sysname -- = null , @currentIsDisabled bit , @currentIsNotTrusted bit , @currentIsNotForReplication bit declare @parentRowCountLessThan bigint declare @referencedRowCountLessThan bigint --set @parent = 'dbo.classSchedules' --set @referenced = 'dbo.student' set @currentIsDisabled = 0 set @currentIsNotTrusted = 1 set @currentIsNotForReplication = 1 --set @parentRowCountLessThan = 2E6 --set @referencedRowCountLessThan = @parentRowCountLessThan exec [dbo].sp_RecreateConstraintForeignKey @parent = @parent ,@referenced = @referenced ,@currentIsDisabled = @currentIsDisabled ,@currentIsNotTrusted = @currentIsNotTrusted ,@currentIsNotForReplication = @currentIsNotForReplication ,@parentRowCountLessThan = @parentRowCountLessThan , @referencedRowCountLessThan = @referencedRowCountLessThan
Review & Apply SQL
Review the generated output.
What we want to capture and apply are:
- Drop Clause
- Create Clause
- Enable Clause
If you will be applying in SSMS, you might want to set the OutputFormat to Text (2).
declare @parent sysname -- = null , @referenced sysname -- = null , @currentIsDisabled bit , @currentIsNotTrusted bit , @currentIsNotForReplication bit , @outputFormat tinyint declare @parentRowCountLessThan bigint declare @referencedRowCountLessThan bigint set @currentIsDisabled = 0 set @currentIsNotTrusted = 1 set @currentIsNotForReplication = 1 set @outputFormat = 1 set @outputFormat = 2 set @parentRowCountLessThan = 1E3 set @referencedRowCountLessThan = @parentRowCountLessThan exec [dbo].sp_RecreateConstraintForeignKey @parent = @parent , @referenced = @referenced , @currentIsDisabled = @currentIsDisabled , @currentIsNotTrusted = @currentIsNotTrusted , @currentIsNotForReplication = @currentIsNotForReplication , @parentRowCountLessThan = @parentRowCountLessThan , @referencedRowCountLessThan = @referencedRowCountLessThan , @outputFormat = @outputFormat
To get the Full SQL Text, you might have to have to ensure that you have extended the “fullness” of each Column.
To do so:
- In SSMS
- Access the menu items – Tools/Option
- The Options Panel appears
- From the left panel, access “Query Results”/”SQL Server”/”Results To Text”
- From the right panel, review the “Maximum number of characters displayed in each column“
System can be impacted as this is not merely a metadata change.
Existing data will have to validated.
Keep in mind
- You want to review the Number of Rows in the Parent & Referenced table as the system will be impacted
- Also, consider
- Running in small batch
- For the running session
- Setting Deadlock Priority very low .i.e. “set deadlock_priority -10“
Implications of Untrusted Constraints
Here are the implications of untrusted constraints:
- The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled ( )
- When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions
- If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
- If a Foreign Key relationship is in place between two tables, inner join queries can skip checking the secondary tables in cases where their columns are not materialized outside of the Join clause; that is those tables where the select clause does not return columns from them
Not for Replication
- “The NOT FOR REPLICATION option is a way of telling SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local value should not be reseeded. Each Publisher using this option gets the same reseeding waiver” – Microsoft
- Brandon Williams writes “When administering Merge Replication, sometimes we need to mark foreign key constraints NOT FOR REPLICATION. In some cases, we might require replication agent activity to be treated differently from user activity since we cannot guarantee the order of changes that are replicated. To be more specific, sometimes parent and child records are sent in different batches, with the children getting sent first, resulting in constraint violations and conflicts – When administering Merge Replication, sometimes we need to mark foreign key constraints NOT FOR REPLICATION
Again, in life when one copies and paste other’s code, one might end up going further that one originally intended to go.
In this case, the “is not trusted” flag was the clue that I needed, as it forced me to dig a lot deeper than my laziness would otherwise allow.
Brandon Williams’ solid explanation was the best I found as why one might want “Not For Replication” on specific and targeted constraints.
Not for Replication / Identity
Not for Replication / Foreign keys
- Brandon Williams – Marking FK Constraints NOT FOR REPLICATION
- Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
- PRB: Non-Convergence When SQL Server Processes Child and Parent Generations in Separate Generation Batches
- How to make foreign key constraints trusted
- Blitz Result: Foreign Keys or Check Constraints Not Trusted
Alter Table /Check Constraint