Temp Tables and Named Constraints

While doing some testing on using NEWSEQUENTIALID() I came across an interesting issue that may come to bite us in the future. I was making a proof of concept of a basic script using temporary tables, something like this (I’ve taken out the unnecessary bits);


IF OBJECT_ID('tempdb..#NewIDTest') IS NOT NULL DROP TABLE #NewIDTest
CREATE TABLE #NewIDTest (RowNum int, ID uniqueidentifier)

ALTER TABLE #NewIDTest ADD CONSTRAINT DF_NewIDTest_ID DEFAULT (newsequentialid()) FOR ID

The whole test script worked just fine, until I sent it to a colleague to help him understand the concept. When he attempted to run the script he was met with the following error;

Msg 2714, Level 16, State 5, Line 4
There is already an object named ‘DF_NewIDTest_ID’ in the database.
Msg 1750, Level 16, State 0, Line 4
Could not create constraint or index. See previous errors.

Turns out that even on (local) temporary tables, constraint names still need to be unique. I ran the first line of the statement above to drop the table and it worked just fine for him. Something to keep in mind for future.

 

2 thoughts to “Temp Tables and Named Constraints”

  1. You can just allow SQL Server to handle the naming on temp tables – like this

    IF OBJECT_ID(‘tempdb..#NewIDTest’) IS NOT NULL DROP TABLE #NewIDTest
    CREATE TABLE #NewIDTest (RowNum int, ID uniqueidentifier default newsequentialid())

  2. Absolutely agree with you here. The only reason I hit this problem is that this was a script to create a permanent table and I just switched it over to a temp table for the proof of concept. If it was a temp table being used in production then absolutely, leave the constraint name out and let SQL server deal with it internally. Thanks for pointing it out 🙂

Leave a Reply