Adding a temporary table with a primary key in multiple sessions


Userlevel 4
Badge +5

Goal

As you may know, multiple sessions can have the same local temporary table (#table_name) at the same time in tempdb. SQL Server solves this by saving the table names unique internally.

However, when using a named primary key on this table that may exist in multiple sessions simultaneously, SQL Server doesn't save the PK constraint name unique internally. The goal is to solve this problem.

Example

In this example we are going to create the temporary table #fk_tables, and add the primary key fk_tables_PK.

create table #fk_tables 
(
ref_id varchar(310) collate database_default,
tab_id varchar(100) collate database_default,
col_id varchar(100) collate database_default,
source_tab_id varchar(100) collate database_default,
source_col_id varchar(100) collate database_default,
abs_order_no int,
CONSTRAINT fk_tables_PK PRIMARY KEY (ref_id,tab_id, col_id)
)

When this same code is executed in a second session, the temporary table will be created but adding the primary key will fail:

Msg 2714, Level 16, State 5, Line 1

There is already an object named 'fk_tables_PK' in the database.

Msg 1750, Level 16, State 1, Line 1

Could not create constraint or index. See previous errors.

Solution

This can be solved by NOT naming the constraint (so by removing CONSTRAINT fk_tables_PK in the example code):

create table #fk_tables 
(
ref_id varchar(310) collate database_default,
tab_id varchar(100) collate database_default,
col_id varchar(100) collate database_default,
source_tab_id varchar(100) collate database_default,
source_col_id varchar(100) collate database_default,
abs_order_no int,
PRIMARY KEY (ref_id,tab_id, col_id)
)

The local temporary table is now added in the second session, including the primary key:

Commands completed successfully.


0 replies

Be the first to reply!

Reply