Search

Temporary tables with named constraints by Joe Pollock

Closed
as Postponed Help for as Postponed

5
2
Sign in
 to vote
Type: Bug
ID: 250046
Opened: 1/5/2007 12:47:08 AM
Access Restriction: Public
1
Workaround(s)
1
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.
Details (expand)
Product Language
English

Version

SQL Server 2005 SP1 - Standard Edition

Category

SQL Engine

Operating System

Win2003 Standard Server (SP1)
Operating System Language
US English
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))

Actual Results
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.
Expected Results
The second table should create.

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by darkmark327 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 darkmark327 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
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))

© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement