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 : 3679 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts