Explicitly Named Temp Table Constraint Generates Error

Explicitly Named Temp Table Constraint Generates Error by Michael MacGregor

Resolved

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	771257
Opened:	11/15/2012 8:18:46 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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.

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows Server 2008 R2 Standard

US English

1. Create instance of temp table with explicitly named PK constraint:
CREATE TABLE #TempTest
(TestID INT IDENTITY CONSTRAINT PK_TempTest_TestID PRIMARY KEY CLUSTERED,
TestDesc VARCHAR(20))

2. Create second instance of temp table while first instance still persists:
CREATE TABLE #TempTest
(TestID INT IDENTITY CONSTRAINT PK_TempTest_TestID PRIMARY KEY CLUSTERED,
TestDesc VARCHAR(20))

Results in following error:
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.

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.

Command successful.

X64

Other (e.g. VM Ware, specify in Description)

0 attachments

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.