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