SQL Server: temporary tables and constraint names

Update. Since the version 2016 SQL Server supports anonymous constraint declaration:

CREATE TABLE #temp_table (
   id1 int NOT NULL,
   id2 int NOT NULL,
   name1 nvarchar(100),
   name2 nvarchar(100),
   PRIMARY KEY (id1, id2),
   UNIQUE (name1, name2)
);

For earlier versions the solution is below.

Local temporary tables are isolated in the scope of current session regardless its name. It means that #temp_table in the session 1 is not the same that #temp_table in the session 2. However, the names of constraints are stored in the metadata of tempdb and can produce duplicate name errors.

CREATE TABLE #temp_table (
   id int NOT NULL,
   name nvarchar(100)
   CONSTRAINT PK_temp_table PRIMARY KEY (id)
)

Open SSMS and run the code in the first session. It's OK. If you open the second session and run it again, the following error will be raised.

Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_temp_table' in the database.

The first solution is simple but it works only:

• for constraints that can be defined inline with the column (primary key, defaults, checks)
• in case of simple key only

This code is OK in multi-sessions.

CREATE TABLE #temp_table (
   id int NOT NULL PRIMARY KEY,
   name nvarchar(100)
)

What do we do in the case of a composite primary key or other constraint types like UNIQUE? Fortunately, the dynamic SQL can do the job!

CREATE TABLE #temp_table (
   id1 int NOT NULL,
   id2 int NOT NULL,
   name nvarchar(100)
);
DECLARE @pk_name sysname, @sql nvarchar(max);
SET @pk_name = 'PK_' + replace(convert(nvarchar(38), newid()), '-', '_');
SET @sql = 'ALTER TABLE #temp_table ADD CONSTRAINT ' + @pk_name +
           ' PRIMARY KEY (id1, id2)';
EXEC sp_executesql @sql;

Note that table index doesn't require to have a globally (database scope) unique name because it should be unique in the scope of the table only. Hence, the following code is OK in multi-sessions.

CREATE TABLE #temp_table (
   id int NOT NULL PRIMARY KEY,
   name nvarchar(100)
);
CREATE INDEX IX1_temp_table ON #temp_table (name);