Primary key constraint on a temporary table

Question

Hi everybody,

I believe I did run into the problem before and even asked a question about, but I don't remember the answer. In the stored procedure I create a temporary table with a primary key (I used the same name for the constraint as the actual table this temp table represented, but with # at the end of the name). All worked well.

Yesterday I wanted to improve the performance of that procedure and so I slightly adjusted the columns in the PK to exactly match the PK from the table we were selecting the data from. I ran the tests several times (creating that temp table and selecting data into it). All worked fine as the ad-hoc query.

So we deployed my changes as a procedure and I got this error 'Error Message: Could not create constraint or index. See previous errors.'

Luckily I remembered I ran into this before. I removed the # from PK's name, re-run the whole process, got this error again. So this time I changed the PK name to the name matching the original PK but with 2 at the end and it worked, but then another procedure failed (which we didn't alter, but I did experiment with the query used by it yesterday too).

So, how can I name (and do I need to name) constraints on the temporary tables? There is
DROP TABLE IF EXISTS #tempTable preceding the temp table creation. It seems to be only failing when it's part of SP.

Thanks a lot in advance.

Answer 1

Unless you have a good reason for naming a constraint on a temporary table, I would not name them. If you execute a statement like

CREATE TABLE #T(i INT, PRIMARY KEY (i));

then while your connection can reference the table as #T, in tempdb, the table name will be something like #T_______00001B and the primary key name will be something like PK__#T_____4A56BC.

If another connection then runs the same statement, that connection also gets a table it can reference as #T, but in tempdb, the table and constraint will have different names (the hex numbers at the end of the names will be different). As you know, that way each connection has it's own copy of #T and it's own primary key constraint.

But if you do

CREATE TABLE #T(i INT, CONSTRAINT T_PK PRIMARY KEY (i));

Then the primary key created in tempdb will be named T_PK. SQL will not add any characters to make the name unique. So if two connections try to have that table and primary key at the same time, SQL will try to create two objects named T_PK and you will get an error since all object names in a database (tempdb in this case) must be unique.

You said you got the error message "Could not create constraint or index. See previous errors.". There really were two error messages (although depending on how your error handler handles multiple error messages, the first one might not be displayed. The first message would have been "There is already an object named 'T_PK' in the database. (Of course, T_PK would be replaced by whatever name you gave your primary key constraint.

The only way you can name the constraint is either 1) only allow one connection at a time to create and use that tempdb, or 2) make sure that each connection uses a different name for the constraint. But I would recommend just not giving the constraint a name.

Tom