No foreign key restraints on a temporary table? SQL Server 2008

Question

I know that a a temporary table will only exist for as long as a session of SQL Server is open, but why can't you have foreign key restraints on them?

Answer 1

Imagine this scenario: You create a foreign key relationship from your temp table to a concrete table's key. One of the restrictions on a foreign key relationship is that you cannot delete a row from a key table that is depended upon by your temp table. Now, generally when you create foreign key relationships you know to delete the dependent table rows before deleting the related rows in the key table, but how does a stored procedure or any other call into the database know to delete rows from your temp table? Not only is it impossible to discover spurious foreign key dependencies, other sessions could not reach your temp table even if it could discover the relationship. This leads to spurious failures in delete statements as foreign key constraints restrict the key table for dependent rows.

Answer 2

You can create foreign keys between tables in tempdb. For example, try this:

use tempdb

create table parent
(
    parent_key int primary  key clustered
)

create table child
(
    child_key int primary key clustered,
    child_parent_key int
)
alter table child add constraint fk_child_parent foreign key (child_parent_key) references parent(parent_key)

insert into parent(parent_key) select 1
insert into child(child_key, child_parent_key) select 1, 1
insert into child(child_key, child_parent_key) select 2, 2 -- this fails because of the FK constraint

drop table child
drop table parent