Table Variable.

Question

Table Variable.

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
September 10, 2009 at 2:15 am

#204508

[font="Verdana"]Hi All,
Why Foreign Key constraint can not be define on Table variable / Temporary Table?
Thanks in advance,
Mahesh[/font]
MH-09-AM-8694

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

temp tables are dropped when you are done with them.

a foreign key prevents something from being dropped before it's child references. If you disconnect your spid, then if foreign keys were permitted in temp tables or variables, the tables would have to be dropped in a specific order. That would add some unnecessary overhead, so

since they are temp tables, there's no value in enforcing a FK relationship.

however, a suite of temp tables can created, and they could all have foreign keys references no problem, but you get this warning:

Skipping FOREIGN KEY constraint '#child' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

for those following along, here's an example that raises an error with table variables

examples:

Table variables cannot refer to other table variables: you get errors like

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '@parent'.

declare @parent table(

parentid int identity(1,1) not null primary key,

parentdata varchar(100) )

declare @child table(

childid int identity(1,1) not null primary key,

--this fails because @parent does not exist in sys.objects, so it's existance cannot be resolved

parentid int references @parent(parentid),

childdata varchar(100) )

here's a pair of valid tables, both created in the tempdb; this will execute without errors., but you get that warning about FK's are not enforced.

--this pair of objects are valid,a s they exist in the same schema

CREATE TABLE #parent(

parentid int identity(1,1) not null primary key,

parentdata varchar(100) )

CREATE TABLE #child (

childid int identity(1,1) not null primary key,

parentid int references #parent(parentid),

childdata varchar(100) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Thanks Lowell. Things get clear to me now.

Mahehs[/font]

MH-09-AM-8694