Rotating Header Image

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….

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

8 Comments

  1. This is a great tip! It was an “a ha” moment for me when I realized that [CONSTRAINT constraint_name] was almost always optional. So that even
    ALTER TABLE #TMP1 ADD PRIMARY KEY CLUSTERED (TheDate)
    works.

  2. Nice!! 🙂 Good call….. thanks for the comment

  3. TK says:

    Good one…I ran into this situation ..did not know why initailly now I know why my dba called it A BUG.

  4. Diego says:

    Thank you!

  5. Matt Potter says:

    I think using SELECT … INTO is very lazy.

    I’d always create my temp table and then insert into it. You don’t always get the data types on the columns you expect doing a SELECT INTO.

    Some interesting thoughts on:
    http://www.sqlservercentral.com/Forums/Topic1123575-392-1.aspx#bm1123585

  6. Pete Thurmes says:

    Laziness is sometimes a very short distance from efficiency.
    I have a fairly static table with only ~1000 rows, but about 30 columns. All I want to do is occasionally create a new row in the table that is very similar to another row – by copying to temp table, changing those few values, and inserting * from the temp table into the org table.

    I guess I do this often enough that I could afford to define each and every column variable in a stored SQL routine, but do I need to?

    My reason for visiting this page is that the table has a PK, and I can’t seem to do either of the following: 1. Insert a PK’d column from the temp table -i.e. the PK row value- into the org table (I understand why, but still, I can’t do it), or 2. Remove the PK from the temp table.
    My SQL server doesn’t show any entries for tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    after creating the temp table, so I can’t find the PK constraint name to remove it (and once I do, and change the previously PK’d field value to NULL, will the INSERT work? )

  7. While there was a time two years ago when everyone and their uncle was on OKCupid, now most girls I know only keep their accounts for the ridiculous messages they get from age-inappropriate weirdos so they can screen-shot it and send it to their friends.

  8. While the design of the Pixel XL and iPhone 7 Plus may carry over from the smaller versions of those handsets, each of these larger editions introduces a new screen, finally giving us something meaty to compare.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.