Temporary tables with named constraints - by Joe Pollock

Status : 

  Postponed<br /><br />
		Due to current priorities, the product team decided to postpone the resolution of this item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


10
2
Sign in
to vote
ID 250046 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/5/2007 12:47:08 AM
Access Restriction Public

Description

When you create a temporary table a unique string is appended to the internal name, so any connection can create a temporary table with the same name and they can all coexist within tempdb. The problem is when you add a primary or foreign key constraint to a temporary table, but only if you specify a name for the constraint. If you do not name it then SQL Server will generate a name which is guaranteed to be unique. Only when you explicitly name it will the table creation fail due to a duplicated constraint name.
Sign in to post a comment.
Posted by marsovo on 12/2/2011 at 1:33 PM
Okay, I see you can use ALTER TABLE #TempTable ADD PRIMARY KEY to do it
Posted by marsovo on 12/2/2011 at 1:29 PM
This is quite a problem if you SELECT INTO a temp table (i.e. you never CREATE the temp table), but then want to add a primary key to it after the fact. There's no way to do it then, because you can't use ALTER TABLE to create an unnamed constraint, right?
Posted by Microsoft on 1/5/2007 at 11:49 AM
Hi,
Thanks for raising this issue. This issue is by design, although this behavior does seem inconsistent. This won't make it for SP2, but we will consider it for future releases of SQL Server.

Regards,
Tomer Verona
SQL Server Development