SQL Server tips and experiences dedicated to my twin daughters.

#0284 – SQL Server – Fun with Temporary Tables – Foreign Keys

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:

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.