Blog » By Colin Stasiuk on Colin Stasiuk

Temp Tables and Primary Keys

Just a quick little post today on a little “gotcha” that may getcha.

Let’s say you’re creating a stored procedure that needs to create a #temp table that has a PRIMARY KEY. You will want to be careful about how you create this #temp table and here is why:

Open SQL Server Management Studio and run the following command:

SELECT	GETDATE() AS 'TheDate'
INTO	#TMP1
ALTER TABLE #TMP1 ADD CONSTRAINT PK_TMP1 PRIMARY KEY CLUSTERED (TheDate)
GO

Now if you open a new Management Studio query window and run the exact same code (simulating what a stored procedure would do) what do you get?

Because you created your PRIMARY KEY constraint after the fact (and had to give it a name) you lose the uniqueness of the PRIMARY KEY name.

SELECT	*
FROM	tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE	TABLE_NAME like '%#TMP1%'

So how do you get around this?

CREATE TABLE #TMP1(TheDate DATETIME PRIMARY KEY)
GO
SELECT	*
FROM	tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE	TABLE_NAME like '%#TMP1%'

and if you run this in 3 different query windows….

Now I used Primary Keys as my example but this same “gotcha” applies to constraints in general. If you were to add a check constraint that validated “TheDate” and gave that check constraint a name you would run into the same “cannot create constraint… duplicate object” error.

Moral of the story: When dealing with temporary tables in reuseable code (like stored procedures) don’t name your constraints.

And now you know….