Database – Constraints – Foreign Keys and Indexes


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


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.






Constraints – Primary Keys

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

		as SchemaName

	, OBJECT_NAME(tblCP.parent_object_id)
		as ObjectName
	, as [Constraint]
	, 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				

	 , OBJECT_NAME(tblCP.parent_object_id)



Constraints – Foreign Keys

List foreign keys and there corresponding indexes.

			as SchemaName

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

		, OBJECT_SCHEMA_NAME(tblFK.referenced_object_id)
			as ReferencedSchema

		, OBJECT_NAME(tblFK.referenced_object_id)
			as ReferencedObject

		, 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				
	, OBJECT_NAME(tblFK.parent_object_id)



Review Referencing Tables Delete

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


SQL Statement:

use [DBLabCommerce]

begin tran tranSave

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

rollback tran tranSave

 Query Plan – Diagram:




 Query Plan – Tabulated


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




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.

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:



 Query Plan – Tabulated


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



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]

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 = 'idx_ProductID'  

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

	print 'Created index commerce.orderDetail.idx_ProductID'	




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


deleteProduct - NonClusteredIndexSeek



 Query Plan – Tabulated

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


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 
            t.object_id as Object_ID,
            s.NAME as SchemaName,
            t.NAME AS TableName,
   as indexName,
            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 
		, s.NAME
		, t.NAME
		, i.object_id
		, i.index_id
		, p.[Rows]


, tblIC 
               tblI.object_id as object_ID
	     , tblI.index_id  as index_ID
             , as indexName
             , tblI.is_disabled as isDisabled
             , 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


		, object_schema_name(tblFKC.parent_object_id)		
		, 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

			on tblFKC.parent_object_id = tblIC.object_id
			and col_name( 
                                       , tblFKC.parent_column_id
                                = tblIC.columnName

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

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


			   (@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') 
	end asc

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



This topic has been covered by so many good and in-depth SQL Developers.  I started off using Kirsten K. Benzel’s public gift @

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


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.




Constraints – Default


Adding data


Foreign Key Indexes



Scripts – Random


Data Models



Leave a Reply

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

You are commenting using your 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