1

Simplified code

var sql = @" create table #Temp( 
                int Id NOT NULL
                CONSTRAINT [PK_TempId] PRIMARY KEY (Id)
             )
             insert into #temp(id) select (Id) from SomeOtherTable
             select Id from #Temp
             "

        using (var db = new MyDbContext())
            {
                var results = db.Database.SqlQuery<int>(sql).ToList();
                return results;
            }

Causes intermittent error

There is already an object named PK_TempId in the database

I have since added

drop table #temp

and am waiting to find if that solves the issue.

Before I added the index, the code ran for years without any problems, and without apparent need to drop the temporary table.

I found a recommendation here that named indexes should not be used in temporary tables. But am not sure of what alternative there is.

3
  • 2
    create table #Temp( Id int PRIMARY KEY ) should work as expected. Aug 4 at 6:41
  • Thank you, the code runs ( I actually am using a composite key) This may be the answer. Given that the issue was intermittent I might not be sure for a while.
    – Kirsten
    Aug 4 at 6:51
  • 1
    The intermittent issue you were seeing was probably due to the sp being run currently. So, letting the system create the unique name (by not explicitly naming it as Denis suggested) should be a good practice whenever you need constraints on temp tables.
    – BCM
    Aug 4 at 16:15
2

As Denis pointed out in the comments, if you remove the name from the primary key portion of your CREATE TABLE script, it should work. This is because the name you were using was already being used for a primary key elsewhere.

This would be because the name PK_TempId was defined on another table, which could either either be a completely different table or a second instance of your #Temp table if your code was above was ran twice concurrently, which is not permitted in the same schema.

Constraint names must be unique within the same schema.

4
  • PK_TempId was only ever used in this SQL, so now I am puzzling how it can have been called concurrently. Would that be due to different users? Clearly the name of the #temp table does not need to be unique in this scenario.
    – Kirsten
    Aug 4 at 23:00
  • 1
    @kirsteng That we wouldn't have the answer to because we don't know how you're using the SQL in your question (e.g. in an application, as an ad-hoc query, in some kind of SQL Server Agent Job that calls a CLR procedure, etc). But somehow, the same primary key name was used twice in the same schema.
    – J.D.
    Aug 4 at 23:23
  • 1
    Thanks J.D. The query is running in a C# application. Thus a second instance of #Temp is possible. I think I understand now. The second instance must not use the same index name even though the same table name (#temp) is OK... but it can use an index without a name.
    – Kirsten
    Aug 5 at 0:17
  • 1
    @kirsteng Yes, correct, because when you don't explicitly specify a name for the primary key constraint, a uniquely randomized one is automatically generated then, by the server.
    – J.D.
    Aug 5 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.