Home Dashboard Directory Help

Temporary tables with named constraints by Joe Pollock


 as Postponed Help for as Postponed

Sign in
to vote
Type: Bug
ID: 250046
Opened: 1/5/2007 12:47:08 AM
Access Restriction: Public
User(s) can reproduce this bug


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
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.

Tomer Verona
SQL Server Development
Sign in to post a workaround.
Posted by JR-J on 1/5/2007 at 12:56 AM
The workaround is to not explicitly name the PK object when defining the temporary table

i.e. CREATE TABLE #Test_with_unique_name_PK (Col int, PRIMARY KEY (Col))