SQL Server – Transact SQL – Script Foreign Keys

Background

Looking at a slow query and found out that one of its main table is a Heap.  And, so knew that that it needed to be recreated as a Clustered Table.

As an aside, the Internet has good discussions around SQL Server’s Clustered Table and Index Organized Tables ( IOT ) in Oracle, but that will have to wait another day.

Index – Recreation

Regular Index

Create Table And Non Clustered Index

If just a regular index:

Code:

Let us create a table and add a non-clustered index.


use [tempdb]
go

if object_id('dbo.tblHeap2015Nov') is not null
begin

  drop table dbo.tblHeap2015Nov;

end
go

create table [dbo].[tblHeap2015Nov]
(
	[id] bigint
)
go

create index [INDX_ID]
on [dbo].[tblHeap2015Nov]
( 
	[id]
)
go

exec sp_helpindex '[dbo].[tblHeap2015Nov]'
go


Output:

normalIndex

Convert Non-Clustered Index To Clustered Index

Let us change the index from non-clustered to Clustered.


create clustered index [INDX_ID]
on [dbo].[tblHeap2015Nov]
( 
	[id]
)
with
	(
		DROP_EXISTING = ON
	)
go

exec sp_helpindex '[dbo].[tblHeap2015Nov]'
go

Output:

normalIndexConvertedToClustered

Heap \ Primary Key

Create Heap (with a non clustered primary key )

Let us create another table, but this time add a non clustered primary key

Code:


use [tempdb]
go

if object_id('dbo.tblHeapPK2015Nov') is not null
begin

   drop table dbo.tblHeapPK2015Nov;

end
go

create table [dbo].[tblHeapPK2015Nov]
(

    [id] bigint

    , constraint [PK_HEAPPK2015NOV] PRIMARY KEY NONCLUSTERED 
	(
		[id]
	)

)
go

exec sp_pkeys 
		  @table_owner = 'dbo'
		, @table_name =  'tblHeapPK2015Nov'
go

exec sp_helpindex '[dbo].[tblHeapPK2015Nov]'
go


Output:

NonClusteredPrimaryKey

Change Non-Clustered Index to Clustered


create clustered index [PK_HEAPPK2015NOV]
on [dbo].[tblHeapPK2015Nov]
( 
  [id]
)
with
(
   DROP_EXISTING = ON
)
go


Output (Text ):


Msg 1907, Level 16, State 1, Line 4
Cannot recreate index 'PK_HEAPPK2015NOV'. The new index definition does not match the constraint being enforced by the existing index.

Output ( Image ):

NewIndexDefinitionDoesNotMatchTheConstraint

Alter Primary Key

If we try to alter primary key …


alter table [dbo].[tblHeapPK2015Nov]
	alter constraint PK_HEAPPK2015NOV
   ......

We will find out, we can not syntactically to do so…

Modify Primary Keys
https://msdn.microsoft.com/en-us/library/ms189251.aspx#TsqlProcedure

To modify a PRIMARY KEY constraint using Transact-SQL:

a) You must first delete the existing PRIMARY KEY constraint

b) And, then re-create it with the new definition

Drop Primary Keys

Code

Here is a sample script for dropping a primary key on a table.


begin tran

  alter table [worktable].[person]
   drop constraint [PK_WORKTABLE_PERSON]
	
rollback tran

Output:

If the targeted Primary Key is referenced by foreign key constraints, we get the error message listed below:

Image:

dropPrimaryKeyErrorWhenReferenced

Textual:

Msg 3725, Level 16, State 0, Line 4

The constraint 'PK_WORKTABLE_PERSON' is being referenced by
table 'personAttribute', foreign key constraint 'FK_WORKTABLE_PERSON'.

Msg 3727, Level 16, State 0, Line 4
Could not drop constraint. See previous errors.

Script Constraints

We see that we likely have to drop and re-create the primary key.

BTW, this applies to all constraints, not just primary keys.

If there are foreign keys referencing the Primary Key, we have to …

  1. Script dependent Foreign Keys
  2. Script the Primary Keys
  3. Drop the Foreign Keys
  4. Drop the Primary Key
  5. Recreate the Primary Key
    • In our case, create as a clustered index
  6. Recreate the referencing Foreign keys

Code

Alan’s Script

Here is a nice script from Alan at Grapefruitmoon


SELECT
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
        + ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
    + ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id) 
        + ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id) 
        + '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
    sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id

Revised Script

We modified Alan’s script the following ways:

  1. Added support for composite keys
    • Without it, we will get the following error:6, Level 16, State 0, Line 5
      There are no primary or candidate keys in the referenced table ‘dbo.ReferencedTable’ that match the referencing column list in the foreign key ‘FK_ReferencingTable_ReferencedTable‘.

use master
go
 
if object_id('dbo.sp_ScriptForeignKeys') is null
begin
 
    exec('create procedure [dbo].[sp_ScriptForeignKeys] as select 1/0 as [shell] ')
     
end
go
 
alter procedure dbo.sp_ScriptForeignKeys
(
      @schema			sysname = null
    , @object			sysname = null
	, @includeDisabled  bit = 0
	, @scriptText	    tinyint = 0
)
as

	set nocount on;

	declare @CHAR_CRLF varchar(30);
	declare @CHAR_COMMAND_SEPARATOR varchar(30);

	set @CHAR_CRLF = char(13) + char(10);
	set @CHAR_COMMAND_SEPARATOR = 'go'

	declare @script TABLE
	(
		  [id]					int not null identity(1,1)

		, [table]				sysname not null

		, [constraint]			sysname not null

		, [isDisabled]			bit 

		, [dropStatement]		varchar(600) not null
		, [createStatement]		varchar(600) not null
		, [enableStatement]		varchar(600) not null
		, [disableStatement]	varchar(600) not null


	)
    ; with ForeignKeyColumnsParent
    as
    (
 
        SELECT
 
                tblFKC.constraint_object_id
 
            , STUFF
                (
                    (
 
                        SELECT ', '
                            + QuoteName(tblSC.name)
 
                        from sys.foreign_key_columns tblFKC_Inner
                         
                        inner join sys.columns tblSC            
 
                        on    tblFKC_Inner.parent_object_id
                                    = tblSC.[object_id]
 
                        and   tblFKC_Inner.parent_column_id 
                                = tblSC.column_id 
 
                        and   tblFKC.constraint_object_id 
                                = tblFKC_Inner.constraint_object_id
 
                        ORDER BY
                            tblFKC_Inner.constraint_column_id
 
                        FOR XML PATH('')
                    )
                    , 1
                    ,1
                    ,''
                ) as columnList
 
 
        from   sys.foreign_key_columns tblFKC
 
        group by
                tblFKC.constraint_object_id
 
    )
    , ForeignKeyColumnsReferenced
    as
    (
 
        SELECT
 
                tblFKC.constraint_object_id
 
            , STUFF
                (
                    (
 
                        SELECT ', '
                                + QuoteName(tblSC.name)
 
                        from sys.foreign_key_columns tblFKC_Inner
                         
                        inner join sys.columns tblSC            
 
                        on    tblFKC_Inner.referenced_object_id
                                    = tblSC.[object_id]
 
                        and   tblFKC_Inner.referenced_column_id 
                                = tblSC.column_id 
 
                        and   tblFKC.constraint_object_id 
                                = tblFKC_Inner.constraint_object_id
 
                        ORDER BY
                            tblFKC_Inner.constraint_column_id
 
                        FOR XML PATH('')
                    )
                    , 1
                    ,1
                    ,''
                ) as columnList
 
 
        from   sys.foreign_key_columns tblFKC
 
        group by
                tblFKC.constraint_object_id
    )
 
	insert into @script
	(
		  [table]

		, [constraint]

		, [isDisabled]

		, [dropStatement]	
		, [createStatement]	

		, [disableStatement]
		, [enableStatement]	

	)
    SELECT
 
          [table]
            = quoteName(ss.name)
                + '.'
                + quoteName(t.name)
 
        , [constraint]
            = QuoteName(fk.NAME)

		, [isDisabled]
			= fk.is_disabled
 
        , DropStatement
            = 
				  ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
				 + 'ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
				+ ' DROP CONSTRAINT '
				+ QuoteName(fk.NAME)
				+ ' ;'
 				+ ' end '
				+ @CHAR_CRLF

        , CreateStatement
            = 
				  ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NULL) '
				 + ' begin '

				 + 'ALTER TABLE '
				 + QuoteName(s.name) + '.'
				 + QuoteName(OBJECT_NAME(fk.parent_object_id))
				 + ' ADD CONSTRAINT '
				 + QuoteName(fk.NAME)
				 + ' FOREIGN KEY ('
				 + cteFKCP.[columnList]
				 + ') '
				 + ' REFERENCES '
				 + QuoteName(ss.name )
				 + '.'
				 + QuoteName
					(
						OBJECT_NAME(fk.referenced_object_id) 
					)
				 + '('
				 + cteFKCR.[columnList]
				 + ');'
				 + ' end '
				 + @CHAR_CRLF

        , DisableStatement
            = ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
				 + ' ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
				+ ' NOCHECK CONSTRAINT '
				+ QuoteName(fk.NAME)
				+ ' ;'
 				+ ' end '
				+ @CHAR_CRLF

        , EnableStatement
            = ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
                 +  'ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
            + ' WITH CHECK CHECK CONSTRAINT '
            + QuoteName(fk.NAME)
            + ' ;'
			+ ' end '
			+ @CHAR_CRLF
 
    FROM  sys.foreign_keys fk
 
    INNER JOIN ForeignKeyColumnsParent cteFKCP
        ON fk.object_id = cteFKCP.constraint_object_id
 
    INNER JOIN ForeignKeyColumnsReferenced cteFKCR
        ON fk.object_id = cteFKCR.constraint_object_id
 
    INNER JOIN sys.schemas s 
        ON fk.schema_id = s.schema_id
 
    INNER JOIN sys.tables t 
        ON fk.referenced_object_id = t.object_id
 
    INNER JOIN sys.schemas ss 
        ON t.schema_id = ss.schema_id
 
    WHERE ss.name = isNull(@schema, ss.name)
  
    and   fk.referenced_object_id
			= isNull
				(
					  object_id(@object)
					, fk.referenced_object_id
				 )  

	and  fk.is_disabled = case
								when (isNull(@includeDisabled, 0) =0) then 0
								when (@includeDisabled =1) then fk.is_disabled
						  end	
 
    order by 1, 2      
	
	if (
			   (@scriptText is null )
			or (@scriptText < 1 )
		)
	begin
	
		select *
		from   @script

	end
	else if (@scriptText = 1 )
	begin

		select [dropStatement] + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 	
		from   @script

	end
	else if (@scriptText = 2 )
	begin

		select [createStatement]  + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 		
		from   @script

	end
	else if (@scriptText = 3 )
	begin

		select [disableStatement]  + @CHAR_COMMAND_SEPARATOR + @CHAR_CRLF 	
		from   @script

	end
	else if (@scriptText = 4 )
	begin

		select [enableStatement]  + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 		
		from   @script

	end
	             
go
 
grant execute on dbo.sp_ScriptForeignKeys to [public]
go
 
EXEC sys.sp_MS_marksystemobject 'dbo.sp_ScriptForeignKeys'
go

Query Plan

Btw, here is a quick comparison between Heaps and Clustered Indexes when the key is searched on.

Heap

Query Plan

heap

Statistics IO

heapStatisticsIO

Clustered Index

Query Plan

ClusteredIndex

Statistics IO

ClusteredIndexStatisticsIO

Explanation

  1. When the Primary Key is an heap
    1. SQL Server accesses both the index and uses the Row ID (RID) to fetch any other data that is not included in the Primary Key
  2. On the other hand, when the Primary Key is a Clustered Index
    1. Only the Clustered Index is accessed, as it contains all data

Summary

In summary, there are two types of indexes in SQL Server.

There are explicitly created indexes defined using “Create Index”.

And, there are implicitly defined ones created using “ALTER TABLE – ADD CONSTRAINT PRIMARY KEY” or “ALTER TABLE – ADD UNIQUE CONSTRAINT

Existing indexes can be modified using DROP_EXISTING. i.e.

DROP_EXISTING = { ON | OFF }

Specifies that the named index is dropped and rebuilt.

On the other hand, constraints definitions are immutable.

And, so prior to re-creating Primary Key constraints and Candidate Keys, we have to first remove dependent FK constraints.

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