Foreign Key and Index Binding

Introductions

In this article I am trying to discuss about the foreign key Index binding features. Hope it will be interesting and we can learn something new from it.

Point in focus

1.    Foreign Key Definitions

2.    Why Index Binding is necessary

3.    What we find in our observations

Let's start it.

Foreign Key Definitions

Here I am just providing a definition of foreign key to understand it properly. If we look at the MSDN

"A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table."

In ideal conditions a foreign key must be liked with the Primary Key constraint in another table. But it is not necessary; it can be defined to reference the columns of a UNIQUE constraint in another table.  

Why Index Binding is necessary

A foreign key referencing the primary key will always be bound to the primary key index. However, we might have additional unique indexes on the primary key column or column(s) for performance reasons.

This Index binding is necessary for two reasons.

Performance Factors

If the parent table is not queried often but rows are inserted into the child table frequently and heavily, a unique non-clustered index that "covers" the referential integrity check may be more desirable than the clustered index.

In SQL server 2008 and later versions preferred unique non-clustered index rather than the clustered index. 

Control the index bound 

If we try to drop an index bound to the foreign key it generate an error message. To do so we must drop the foreign key first and then drop the index.

What we find in our observations

Now we see a simple example of foreign key Index binding and observe the result very carefully.

Step-1 [ Creating Parent Table ]

IF OBJECT_ID('ChildTable') IS NOT NULL

   BEGIN

     DROP TABLE ChildTable;

   END

GO

IF OBJECT_ID('ParentTable') IS NOT NULL

   BEGIN

     DROP TABLE ParentTable;

   END

GO

CREATE TABLE dbo.ParentTable

       (PtblID INT         NOT NULL IDENTITY

               CONSTRAINT PK_ParentTable 
               PRIMARY KEY CLUSTERED,

        Col1   INT         NOT NULL,

        Col2   VARCHAR(50) NOT NULL

       );

GO

Step-2 [ Creating non Clustered Covered Index ]

CREATE UNIQUE NONCLUSTERED INDEX idx_PTable_PtblID

       ON dbo.ParentTable(PtblID) INCLUDE(Col1);

Step-3 [ Inserting some records in Parent Table ]

INSERT INTO dbo.ParentTable VALUES(1, 'Joydeep Das');

INSERT INTO dbo.ParentTable VALUES(2, 'Sukamal Jana');

INSERT INTO dbo.ParentTable VALUES(3, 'Tuhin Shinah');

GO

Step-4 [ Creating Child table with Foreign Key ]

CREATE TABLE dbo.ChildTable

       (CtblID   INT NOT NULL IDENTITY

                 CONSTRAINT PK_ChildTable PRIMARY KEY CLUSTERED,

        PtblID   INT NOT NULL 
                 CONSTRAINT FK_ChildTable_ParentTable

                 FOREIGN KEY REFERENCES dbo.ParentTable(PtblID)

       );

GO

Step-5 [ Inserting Records to Child Table ]

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(1);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(2);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

INSERT INTO dbo.ChildTable VALUES(3);

GO

Step-6 [ Update the Statistics ]

UPDATE STATISTICS dbo.ParentTable;

UPDATE STATISTICS dbo.ChildTable;

GO

Step-7 [ Finding the Foreign key Binding ]

SELECT a.name As [FK Binding]

FROM   sys.foreign_keys f

       INNER JOIN sys.indexes a 
       ON a.object_id = f.referenced_object_id    

          AND a.index_id = f.key_index_id

WHERE  f.object_id = OBJECT_ID(N'dbo.FK_ChildTable_ParentTable');

GO

Now lets execute the above scrips in different version of the MS SQL Server.

MS SQL Server 2005

The output of the step-7 is

FK Binding

PK_ParentTable

SQL Server 2005 chooses the clustered index when possible and, if no suitable clustered index exists, then the unique non-clustered index on the referenced column is used.

SQL 2008, SQL 2008R2 and SQL 2012

FK Binding

idx_PTable_PtblID

The foreign key is bound to the unique non-clustered index on the referenced column.

Hope you like it.

Posted by: MR. JOYDEEP DAS