SQL Server – Constraints – Recreating Foreign keys

Introduction

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.

Code

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]

Output:

identifiedRecords.20151217

Identify Untrusted Objects ( generic )

Identify untrusted objects

Script

 

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

Output:

identifyUntrustedConstraintsGeneric

Check Constraints

Before enabling, we should probably check existing data

DBCC CheckConstraints

DBCC CheckConstraints – Table

Syntax

DBCC CHECKCONSTRAINTS ('table-name');

Sample

DBCC CHECKCONSTRAINTS ('[dbo].[StudentCalendar]');

Output:

If there are records in the Parent table, that are missing in the referenced table, they will be listed.

dbccCheckConstraint

 

DBCC CheckConstraints – Foreign Key

As one would expect, it is cheaper to target a specific constraint rather than all constraints on the table.

Syntax

DBCC CHECKCONSTRAINTS ('foreign-key');

Sample

DBCC CHECKCONSTRAINTS ('[dbo].[FK_InstructorGroupProducts_ProductCatalog]');

Check Data

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:

  1. Left Outer Join
  2. 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

 

Except


-- using except
select 
		  tblADS.[id]
		, tblADS.[dealid]	
from   [dbo].AssociateDealsSchools tblADS

except

select 
		  tblAD.[id]
		, tblAD.[dealid]	
from   dbo.AssociateDeals tblAD


Execution Plan

The query plan for the available pathways is similar.

LegacyMethods

 

Repair

Altering Existing Constraint

In cases where “is not for replication” is off, we can enable by altering the existing constraint.

Syntax


  ALTER TABLE parent-table
	with CHECK CHECK CONSTRAINT [constraint-name]

 

Sample


 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.

Steps

And, so what we need to do :

  1. Capture the Foreign Key Constraint Creation Step
  2. Drop the Foreign Key
  3. Review the captured SQL and remove “Not for replication” if it be there
  4. Apply the edited SQL
  5. 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

Sample Invocation

Grid


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

Output:

identifiedRecords

 

Review & Apply SQL

Review the generated output.

What we want to capture and apply are:

  1. Drop Clause
  2. Create Clause
  3. Enable Clause

 

Textual

 

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	

Output:

capturedSQL

 

Customization:

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:

  1. In SSMS
  2. Access the menu items – Tools/Option
  3. The Options Panel appears
  4. From the left panel, access “Query Results”/”SQL Server”/”Results To Text”
  5. From the right panel, review the “Maximum number of characters displayed in each column

 

SSMSOptionsBefore

 

Impact

System can be impacted as this is not merely a metadata change.

Existing data will have to validated.

I/O

Statistics I/O

statisticsIO

 

Keep in mind

  1. You want to review the Number of Rows in the Parent & Referenced table as the system will be impacted
  2. Also, consider
    • Running in small batch
    • For the running session
      • Setting Deadlock Priority very low .i.e. “set deadlock_priority -10

 

Implications

Implications of Untrusted Constraints

Here are the implications of untrusted constraints:

  1. The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled ( hintCurly)
  2. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions
  3. 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.

Performance Implication

Trusted Constraints

  1. 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

Identity Columns

  1. 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

 

Foreign keys

  1. 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 hintOnGreenBackground

 

Summary

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.

References

Constraints Trusted?

  1. Can you trust your constraints

 

Not for Replication / Identity

  1. Using not for replication

 

Not for Replication / Foreign keys

  1. Brandon Williams – Marking FK Constraints NOT FOR REPLICATION
  2. Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
  3. PRB: Non-Convergence When SQL Server Processes Child and Parent Generations in Separate Generation Batches
  4. How to make foreign key constraints trusted
  5. Blitz Result: Foreign Keys or Check Constraints Not Trusted

 

DBCC CheckConstraints

  1. A Look At DBCC CHECKCONSTRAINTS and I/O

 

Join Elimination/Oracle

  1. Oracle-base

 

Alter Table /Check Constraint

  1. Alter Table / Check Constraint

 

2 thoughts on “SQL Server – Constraints – Recreating Foreign keys

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s