There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply