Database – Constraints – Foreign Keys and Indexes

Background

This is a follow-up post to our initial discussion on Candidate Keys.  In this post we will discuss Foreign Keys index requirements.

 

Source Control – Github

The SQL Source code files have been placed into Github.

The URL is https://github.com/DanielAdeniji/MicrosoftSQLServerForeignKeysIndexRequirements

 

List of Files

Here is the list of files:

 

File Name What is it
DataModel / CommerceLogicalModel.png Logical Data Model – Created using Database Diagram in Microsoft SQL Server Management Studio – Screen Shot
Query Plan / deleteProduct – ClusteredIndexScan.png Query Plan – Clustered Index Scan ( pre-creation of index )
Query Plan / deleteProduct – NonClusteredIndexSeek.png Query Plan – Non-Clustered Index ( post-creation of index )
Query Plan / deleteStore.png Query Plan – Delete Store
Script / DDL / DDLCreate.sql Create all database objects
Script / DDL / DDLDrop.sql Drops all database objects
Script / DDL / commerce.orderDetail.idx_ProductID.sql Not included in batch of objects initially created; created once we realized we need an index
Script / DML / deleteRecordsFromTableProduct.sql Delete records from Products table;  this is not part of initial objects created; used when evaluating model
 Script / DML / deleteRecordsFromTableStore.sql Delete records from Store table;  this is not part of initial objects created; used when evaluating model
 Script/ DML / 1.PopulateShipmentMethod.sql  Scripts 1 through 8 are the sets of scripts that inserts data into our tables
 Script/ DML / 2.PopulateStore.sql
 Script/ DML / 3.PopulateCustomer.sql
 Script/ DML / 4.PopulateCustomerAddress.sql
 Script/ DML / 5.PopulateProduct.sql
 Script/ DML / 6.PopulateOrder.sql
 Script/ DML / 7.PopulateOrderDetail.sql
 Script/ DML / 8.PopulateOrderShipment.sql
Script / metadata / ForeignKeysMissingIndexes.sql  List Foreign Keys missing indexes on the Referencing Objects
 Script / metadata / listForeignKeys.sql  List foreign keys
 Script / metadata / listPrimaryKeys.sql List primary keys

 

 

 

Data Model

Data Model – Logical

Here is a logical view of our Data Model.

 

CommerceLogicalModel

 

Metadata

 

Constraints – Primary Keys

To list the primary keys we will run a script similar to what we have below.


select 
	  SCHEMA_NAME(tblCP.schema_id) 
		as SchemaName

	, OBJECT_NAME(tblCP.parent_object_id)
		as ObjectName
			
	, tblCP.name as [Constraint]
		
	, tblInd.name as [Index]
		
	, tblInd.is_disabled as isDisabled

from    sys.key_constraints tblCP

	   inner join sys.indexes tblInd
			
	      on tblCP.parent_object_id = tblInd.object_id
	      and tblInd.is_primary_key = 1

order by				
	   SCHEMA_NAME(tblCP.schema_id) 

	 , OBJECT_NAME(tblCP.parent_object_id)

Output:

listPrimaryKey

Constraints – Foreign Keys

List foreign keys and there corresponding indexes.


select 
		  SCHEMA_NAME(tblFK.schema_id) 
			as SchemaName

		, OBJECT_NAME(tblFK.parent_object_id)
			as ObjectName
			
		, tblFK.name as [Constraint]

		, OBJECT_SCHEMA_NAME(tblFK.referenced_object_id)
			as ReferencedSchema

		, OBJECT_NAME(tblFK.referenced_object_id)
			as ReferencedObject

		, tblInd.name as indexName
				
		, tblFK.key_index_id

		, tblFK.is_disabled as isDisabled		

		, tblFK.is_not_trusted as isNotTrusted		
		
		
from    sys.foreign_keys tblFK

		inner join sys.indexes tblInd
			
			on tblFK.parent_object_id = tblInd.object_id
			and tblFK.key_index_id = tblInd.index_id


order by				
	  SCHEMA_NAME(tblFK.schema_id) 
	, OBJECT_NAME(tblFK.parent_object_id)

Output:

listForeignKey

Review Referencing Tables Delete

 

commerce.store

We will issue a delete statement against [commerce].[store].

 

SQL Statement:


use [DBLabCommerce]
go

begin tran tranSave

	delete  tblStore
	from    [commerce].store tblStore
	where   tblStore.storeID = 10

rollback tran tranSave

 Query Plan – Diagram:

 

deleteStore

 

 Query Plan – Tabulated

 

Operation Target Percentile
Clustered Index Delete store.PK_Store.tblStore 80%
Clustered Index Seek  [order].[PK_Order]  20%
 Nested Loops
 Assert

 

 

Explanation:

When we issue a delete again the Store table, it checks the referencing tables, in our case the Order table.  As the check against the referenced table is performed using a Clustered Index Seek, we are good.

 

 

commerce.store

We will issue a delete statement against [commerce].[product].

 

Clustered Index Scan

SQL Statement:


begin tran tranDeleteProduct

	delete  tblProduct
	from    [commerce].[product] tblProduct
	where   tblProduct.PRODUCT_ID = 40

rollback tran tranDeleteProduct

 

Query Plan:

deleteProduct

 

 Query Plan – Tabulated

 

Operation Target Percentile
Clustered Index Delete product.PK_Product.tblProduct 80%
Clustered Index Scan  [orderDetail].[PK_OrderDetail]  20%
 Nested Loops
 Assert

 

Explanation:

When we issue a delete again the Product table, it again checks the referencing tables, in our case the OrderDetail table.  It checks the referenced table using a Clustered Index Scan.

Clustered Index Scan” is a catch-all index; let us see if we can create a more specific index.

Non-clustered Index Seek

Let us go create an index:

 


use [DBLabCommerce]
go

if (
	OBJECT_ID('commerce.orderDetail') is not null
		    
	and not exists
		(
		  select 1
		  from   sys.indexes tblIdx
		  where  tblIdx.object_id = OBJECT_ID('commerce.orderDetail')
		  and    tblIdx.name = 'idx_ProductID'  
		)
	)
					
begin

	print 'Creating index commerce.orderDetail.idx_ProductID ....'
	
		create index idx_ProductID
		on [commerce].[orderDetail]
		(
			[Product_ID]
		)

	print 'Created index commerce.orderDetail.idx_ProductID'	

end		

 

 


SQL Statement: Let us re-run our query.

 

begin tran tranDeleteProduct

	delete  tblProduct
	from    [commerce].[product] tblProduct
	where   tblProduct.PRODUCT_ID = 40

rollback tran tranDeleteProduct

Output:

deleteProduct - NonClusteredIndexSeek

 

 

 Query Plan – Tabulated

Operation Target Percentile
Clustered Index Delete product.PK_Product.tblProduct 80 %
Index Seek  [orderDetail].[idx_ProductID] 20 %
 Nested Loops
 Assert

 

We now have a nice index seek.  Interestingly enough the percentile is still same at 20%.

In follow-up posts we will dig a bit further into how to better compare the before and after scenarios.

 

 

Identify missing Foreign Key Indexes

In this section we will quickly introduce a script that can be used to identify Foreign Key Constraints that are are missing indexes on the referencing tables.

 


declare @filterOnlyIncludeEntriesMissingIndexes bit
declare @orderBy                                sysname
declare @referencedObject                       sysname 

set @filterOnlyIncludeEntriesMissingIndexes = 1
set @orderBy = 'SourceTable'
--set @orderBy = 'SourceNumberofRecords'


;with NumberofRecords AS 
    (  
          
        SELECT 
            t.object_id as Object_ID,
            s.NAME as SchemaName,
            t.NAME AS TableName,
            i.name as indexName,
            p.[Rows],
            sum(a.total_pages) as TotalPages, 
            sum(a.used_pages) as UsedPages, 
            sum(a.data_pages) as DataPages,
            (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
            (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
            (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
        FROM sys.tables t
			INNER JOIN sys.indexes i 
				ON t.OBJECT_ID = i.object_id
			INNER JOIN sys.schemas s 
				ON t.SCHEMA_ID = s.schema_id
			INNER JOIN sys.partitions p 
				ON  i.object_id = p.OBJECT_ID 
				AND i.index_id = p.index_id
			INNER JOIN sys.allocation_units a 
				ON p.partition_id = a.container_id
        WHERE t.NAME NOT LIKE 'dt%' 
        AND   i.OBJECT_ID > 255 
        AND   i.index_id <= 1
        GROUP BY 
		  t.object_id
		, s.NAME
		, t.NAME
		, i.object_id
		, i.index_id
		, i.name
		, p.[Rows]


    )

, tblIC 
as
   (
             
       select
               tblI.object_id as object_ID
	     , tblI.index_id  as index_ID
             , tblI.name as indexName
             , tblI.is_disabled as isDisabled
             , tblC.name as columnName                       
             , tblIC.index_column_id
                  
        from   sys.indexes tblI
 
	         inner join sys.index_columns tblIC              
                          
		   on  tblI.object_id = tblIC.object_id
		   and tblI.index_id = tblIC.index_id
							                             
		inner join sys.columns tblC
                             
		   on tblIC.object_id = tblC.object_id
		   and tblIC.column_id = tblC.column_id

   ) 


select 
		  tblFK.name
		
		, object_schema_name(tblFKC.parent_object_id)		
			referencingSchema
			
		, object_name(tblFKC.parent_object_id) 
			as referencingObject
		
		, col_name(tblFKC.parent_object_id
					, tblFKC.parent_column_id) 
			as referencingColumn

		, object_schema_name(tblFKC.referenced_object_id)		
			as referencedSchema

		, object_name(tblFKC.referenced_object_id) 
			as referencedObject

		, col_name(tblFKC.referenced_object_id
				, tblFKC.referenced_column_id) 
			as referencedColumn

		, tblNR.[rows] as NumberofRecords		
		
		, tblIC.indexName as indexName
		
		, tblIC.isDisabled as indexIsDisabled
		
		, tblIC.columnName as columnName						
		
		, tblIC.index_column_id as indexColumnID

		
from  sys.foreign_keys tblFK

		INNER JOIN sys.foreign_key_columns tblFKC
		
			ON tblFK.object_id = tblFKC.constraint_object_id

		LEFT OUTER JOIN tblIC
					
			on tblFKC.parent_object_id = tblIC.object_id
			and col_name( 
                                         tblFKC.parent_object_id
                                       , tblFKC.parent_column_id
                                    )     
                                = tblIC.columnName

		inner join NumberofRecords tblNR			
		
			on tblFKC.parent_object_id = tblNR.Object_ID

where
        (
            
                (
                        (@filterOnlyIncludeEntriesMissingIndexes =0) 
                     or (
			      (@filterOnlyIncludeEntriesMissingIndexes =1) 
			   and ( tblIC.indexName is null)
			)							
                )

		and

		   (
			   (@referencedObject is null)
			or (tblFKC.referenced_object_id =          
                              object_id(@referencedObject) )

		   )
        )			
        
order by
 
       case when (@orderBy = 'SourceNumberofRecords') 
			then tblNR.[rows] end desc
 
     , case when (@orderBy = 'referencedObject') 
            then
                   object_name(tblFKC.referenced_object_id) 
	end asc

     , case when (@orderBy != 'SourceNumberofRecords') 
			then 
			  	   object_schema_name(tblFKC.parent_object_id)       
				  + object_name(tblFKC.parent_object_id) 
				  + col_name(tblFKC.parent_object_id, tblFKC.parent_column_id) end asc
 
go

 

Dedicated

This topic has been covered by so many good and in-depth SQL Developers.  I started off using Kirsten K. Benzel’s public gift @ http://www.ksql.co/missingix_supportingfk.html.

Have a lot of respect for her upon meeting her at the last two “San Francisco Microsoft SQL Server User Group” meetings.

I will as well be remiss if I do not include a couple of Oracle bloggers and writers who did an handsome job writing on same subject. As everyone knows there are very sturdy workmen in the Oracle & DB/2 camp, as well.

I enjoyed Tom Kytes’ Q/A and Franck Pachot’s presentation. Both are noted in the Reference section.

In summary, that is why we write, to quickly give credit and not pretend others have not discovered and placed in the Public Square.

Listening to ..

Listening to :

Javier Colon – How Many People Can Say That
https://www.youtube.com/watch?v=yFtg6Buv-Zw

Summary

There is a lot to be said about blind spots in Database design.

In this case, we touched on the fact that referenced tables need to have primary or candidate keys.

On the other-hand, the referencing tables might need indexes, as well; especially when the referenced key is being deleted or changed and the referencing objects needs to be checked for records that might end up being orphaned.

 

References

 

Constraints – Default

 

Adding data

 

Foreign Key Indexes

 

Scripts

Scripts – Random

 

Data Models

 

Oracle

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