There is a very interesting aspect of temporary tables – in many aspects they are very different from the “regular” user tables. One such area is the constraints that temporary tables support. We have already looked at named constraints in a previous post.

Today, we will look at foreign keys on temporary tables.

The rule

Temporary tables DO NOT support foreign key constraints.

The rule above says it all – temporary tables do not support foreign key constraints. If we explicitly attempt to define a foreign key constraint on a temporary table, we receive the following message:

Skipping FOREIGN KEY constraint ‘fk_temployeeList_HREmployee’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

To validate this, I wrote the following script. The script creates a local temporary table with a primary key defined on it. I later go on to define a foreign key constraint with the HumanResources.Employee table in the AdventureWorks database.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('tempdb..#employeeList','U') IS NOT NULL
DROP TABLE #employeeList;
GO

CREATE TABLE #employeeList 
    (ListId           INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
     BusinessEntityID INT     NULL,
    );

--FK creation
ALTER TABLE #employeeList
ADD CONSTRAINT fk_temployeeList_HREmployee 
    FOREIGN KEY (BusinessEntityID) 
    REFERENCES HumanResources.Employee(BusinessEntityID)
GO

/**********
RESULTS 01
**********/
/*
Skipping FOREIGN KEY constraint 'fk_temployeeList_HREmployee' definition for temporary table.
FOREIGN KEY constraints are not enforced on local or global temporary tables.
*/

The following script also helps us validate that the primary key was created, but the foreign key was not created.

-- Step 02: Confirm that the primary key was created, but the foreign key was not
IF OBJECT_ID ('tempdb..#employeeList','U') IS NOT NULL
BEGIN
    SELECT skc.name AS PrimaryKeyConstraintName
    FROM tempdb.sys.key_constraints AS skc
    WHERE skc.parent_object_id = OBJECT_ID ('tempdb..#employeeList','U')
      AND skc.type = 'PK'
      AND skc.is_ms_shipped = 0;

    SELECT psc.name                               AS ParentColumnName,
           OBJECT_NAME(sfkc.referenced_object_id) AS ReferencedTable,
           rsc.name                               AS ReferencedColumnName
    FROM tempdb.sys.foreign_key_columns AS sfkc
    INNER JOIN tempdb.sys.columns AS psc ON sfkc.parent_column_id = psc.column_id
                                        AND sfkc.parent_object_id = psc.object_id
    INNER JOIN tempdb.sys.columns AS rsc ON sfkc.referenced_column_id = rsc.column_id
                                        AND sfkc.referenced_object_id = rsc.object_id
    WHERE sfkc.parent_object_id = OBJECT_ID('#employeeList');
END
GO

/**********
RESULTS
**********/
/*
PrimaryKeyConstraintName
-------------------------------
PK__#employe__E38328055A6D792F

ParentColumnName  ReferencedTable  ReferencedColumnName
----------------- ---------------- ---------------------

*/

Hope that you found this post interesting. Do let me know your feedback in the comments area below before leaving.

Until we meet next time,

Be courteous. Drive responsibly.