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.
HI ,
Really Its interesting.
Thanks.
LikeLike
Pingback: #0376 – SQL Server – Msg 2714: There is already an object named ‘#tableName’ in the database. | SQLTwins by Nakul Vachhrajani
Pingback: #0352 – SQL Server – Working with Temporary Tables (L100) – Scope | SQLTwins by Nakul Vachhrajani