I was recently developing a stored procedure and ended up with an strange error during unit testing – the stored procedure failed whenever two connections executed the stored procedure while the other connection was active.

I was able to attribute the error to an oversight I had made with respect to temporary table handling and that incident triggered some reading and the posts this week.

The Error

Here is a gist of what my temporary table creation script looked like:

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedPK','U') IS NOT NULL
    DROP TABLE #tableWithNamedPK;
GO

CREATE TABLE #tableWithNamedPK 
    (RecId    INT,
     RecValue VARCHAR(20),
     CONSTRAINT pk_tableWithNamedPK PRIMARY KEY CLUSTERED (RecId)
    );
GO

Whenever I run the same script from a different SSMS window (thereby simulating two or more users executing the script through the application), I ended up with the following error:

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

The Explanation

The explanation is actually quite simple – when local temporary tables are created, there is a different instance of the temporary table created in the tempdb (Refer my post on the temporary table naming convention  for a detailed explanation). If we define named constraints, all these tables will have the same named constraint, which is not valid!

In my case the named constraints defined on my temporary tables ensured that only one user connection at a time can execute the statement that creates the temporary table.

Not restricted to primary keys

This thumb-rule is not restricted to primary keys alone. This is applicable to any named constraint. To demonstrate this in this post, I wrote this little script:

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedUK','U') IS NOT NULL
    DROP TABLE #tableWithNamedUK;
GO

CREATE TABLE #tableWithNamedUK 
    (UniqueExternalSystemId INT,
     RecValue               VARCHAR(20),
     CONSTRAINT uk_tableWithNamedUK_ExternalSystemId
     UNIQUE NONCLUSTERED (UniqueExternalSystemId)
    );
GO

To test it out, open two SSMS query editor windows. You can execute this in SSMS Query editor window #1 and #2, and the following error will be seen for query editor window #2.

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

The Solution

The question therefore comes is: How can one define constraints on temporary tables?

The devil is in the details and if we re-read the thumb-rule highlighted above, we realize that the restriction is not on the definition of the constraint – it’s on the definition of named constraints.

The following script therefore will work in multiple concurrent connections. The script also contains a code snippet to identify the name of the system generated constraint names.

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedPK','U') IS NOT NULL
    DROP TABLE #tableWithNamedPK;
GO

CREATE TABLE #tableWithNamedPK 
    (RecId    INT,
     RecValue VARCHAR(20),
     PRIMARY KEY CLUSTERED (RecId)
    );
GO

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedUK','U') IS NOT NULL
    DROP TABLE #tableWithNamedUK;
GO

CREATE TABLE #tableWithNamedUK 
    (UniqueExternalSystemId INT,
     RecValue               VARCHAR(20),
     UNIQUE NONCLUSTERED (UniqueExternalSystemId)
    );
GO

--Check the primary key names
SELECT skc.name AS ConstraintName,
       skc.type_desc AS ConstraintType
FROM tempdb.sys.key_constraints AS skc
WHERE ( skc.parent_object_id = OBJECT_ID ('tempdb..#tableWithNamedPK','U')
        OR
        skc.parent_object_id = OBJECT_ID ('tempdb..#tableWithNamedUK','U')
      )
      AND 
      ( skc.type = 'PK' OR skc.type = 'UQ' )
  AND skc.is_ms_shipped = 0;
GO

/*********************
RESULTS 03
*********************/
/*
ConstraintName                  ConstraintType
------------------------------- -----------------------
PK__#tableWi__360414DF6D0EF7FB  PRIMARY_KEY_CONSTRAINT
UQ__#tableWi__D9712AD5EC6B160E  UNIQUE_CONSTRAINT
*/

Please do let me know your feedback, and have a great time working with temporary tables today!

Until we meet next time,

Be courteous. Drive responsibly.