Explicitly Named Temp Table Constraint Generates Error - by Michael MacGregor

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
1
Sign in
to vote
ID 771257 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/15/2012 8:18:46 AM
Access Restriction Public

Description

Explicityly naming a constraint on a temp table will cause an error as the constraint names have to be unique in the system tables. 

SQL Server will add a unique suffix to the temp table name to allow multiple instances of that temp table to exist, but it does not do the same thing with explicitly named constraints on the temp tables, instead the constraint is named exactly as specified in the DDL. 

Either explicitly named constraints on temp tables should behave the same way as the table name itself, i.e. a unique suffix is added, or it should not be allowed.

Interestingly enough table variables do not allow the PK constraint to be explicitly named.

To reproduce:

CREATE TABLE #TempTest
(TestID INT IDENTITY CONSTRAINT PK_TempTest_TestID PRIMARY KEY CLUSTERED,
TestDesc VARCHAR(20))

The temp table is created without issue. However if the same code is run under a different connection to the database while the first instance of the temp table still persists, the following error occurs:

Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_TempTest_TestID' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

I have found this behaviour to be consistent in SQL Server 2000, 2005, 2008 & 2008 R2, with all SPs and regardless of whether SQL Server is in a VM environment or not.
Sign in to post a comment.
Posted by Microsoft on 2/12/2013 at 7:30 AM
Thanks Michael for reporting the issue around explicit constraint naming with temp tables. The name space in this case is scoped to the database and not the table. Mitigation is to specify a unique constraint name within the tempdb scope or not specifiy a constraint name allowing the system to generate unique name within the tempdb scope. This behavior has been consistent across previous releases,no plans to change but will keep in mind for future releases.