Dealing with temporary tables and named constraints

The other day one of our recently changed stored procedures in the development environment started to fail with a message like the following

There is already an object named 'PK_#SomeName' in the database.

I looked at the proc code and noticed something like the following

ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Books On Line specifies the following about constraint names

constraint_name

Is the name of a constraint. Constraint names must be unique within the schema to which the table belongs.

Before I give you an example of how you can get around this, let's first see how it breaks

Copy and paste the following code in a window in SQL Server Management Studio, execute the code below

CREATE TABLE #test (id int not null)


ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Now take the same exact code, paste it in a new window (use the same database) and execute it, you should see the following error

Msg 2714, Level 16, State 5, Line 3
There is already an object named 'PK_#test' in the database.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

As you can see the message clearly tells you that there is already an object with that name in the database. So how can you get around this? There are two ways, the first is to use an unnamed constraint

Open a new window and execute the following

CREATE TABLE #test (id int NOT NULL, PRIMARY KEY (id))

)

You can now do this in a couple of new windows and it won't fail.

Just to prove that the constraint works as expected, run the following code in some of those windows

INSERT #test VALUES(1)
INSERT #test VALUES(1)

You will get a message similar to the one below

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__#test_____3213E83F8E75389B'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is (1).
The statement has been terminated.

Instead of a primary key, you could also use a unique index. Contrary to constraints names, index names do not have to be unique within the schema to which the table belongs

You can run the following code in a couple of windows and it won't fail

CREATE TABLE #test (id int not null)

CREATE UNIQUE CLUSTERED INDEX PK_#test on #test(id)

If you run the code below you will see that it will fail on the second insert

INSERT #test VALUES(1)

INSERT #test VALUES(1)

Here is the expected error message

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#test' with unique index 'PK_#test'. The duplicate key value is (1).
The statement has been terminated.

So to sum it up, do NOT use named constraint with temporary tables, especially not inside stored procedures, if two sessions run the same proc you will get a failure


Use unnamed constraints or use a unique index on a non nullable column instead