Kindly Clarify: Why we can't enforce Referential Integrity(FK) on Temp tables ?

Posted by Jasminej under Sql Server on 4/19/2012 | Points: 10 | Views : 1347 | Status : [Member] | Replies : 2
Why we can't enforce Referential Integrity(FK) on Temp tables ?

I would like to know the technical reason and sample! Not only theoritical explanation




Responses

Posted by: Sabarimahesh on: 4/19/2012 [Member] Bronze | Points: 25

Up
0
Down
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

This Link Having Elaboarate answer


Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/27/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Enforcing Referential Integrity on Temp table (Local and Global) not supported/possible!

The reason may be one/all of the followings..

1. #Table (Local Temp Table) is the scope/session level. Out of the session/scope It can not be accessed.
2. ##Table (Global Temp Table) is the session/connection level. It can not be accessed once the source connection/session where it originally created is Closed.
- So, What will happen when referring/forcing the Constraint on it ?
(or)
- Once restarted the service, the Tempdb database will be recreated, So all the user defined objects also will not be there!

One of the following error will be thrown:

Foreign key references to temporary tables are not supported
(or)
Skipping FOREIGN KEY constraint '#Table' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response