SQL Server Home
Temporary tables with named constraints
1/5/2007 12:47:08 AM
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.
SQL Server 2005 SP1 - Standard Edition
Win2003 Standard Server (SP1)
Operating System Language
Steps to Reproduce
Run the following code in two different connections to the same SQL Server:
CREATE TABLE #Test_with_named_PK (Col int, CONSTRAINT Test_PK PRIMARY KEY (Col))
Second execution will fail with the error:
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'Test_PK' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The second table should create.
to post a comment.
Please enter a comment.
on 12/2/2011 at 1:33 PM
Okay, I see you can use ALTER TABLE #TempTable ADD PRIMARY KEY to do it
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?
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.
SQL Server Development
to post a workaround.
Please enter a workaround.
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))
© 2013 Microsoft