There is already an object named '%constraintname%' in the database.

If you use a lot of temporary tables, you may get the silly error, above.  Run the following in a query window:

USE Test
DROP TABLE #myTable
GO
CREATE TABLE #myTable (ID int NOT NULL)
GO
ALTER TABLE #myTable
    ADD CONSTRAINT pk_myTable PRIMARY KEY (ID)
GO
INSERT INTO #myTable VALUES (1)
GO

(1 row(s) affected)

Now crack open another query window and run the same snippet. Here’s what you’ll get:

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'pk_myTable' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

(1 row(s) affected)

There interesting thing here was pointed out to me by my stealth-DBA associate Harrison Doan today, because I was debugging a process while in another spid the actual production process was dying. Nice, right? What’s happening here is that while some smart guy knew to create temporary tables with a guid and long line of underscores attached to the back of a table, some bozo decided NOT to do same with constraints, hence the error. As you know you can’t have the same constraint name twice in a database.

Here’s the view from sys.sysobjects – you can see that one constraint gets created, but the other doesn’t. Note the parent object:

Here’s the kicker. Open the image again and notice the table #108B795B. I have no idea what it is, some table created by SQL Server. Looks closely right below this entry…the idiot attached that GUID to the back of the PK, but didn’t bother to in our case! WTH??? You can see the primary key for the above table, PK__#108B795__727E83EB1273C1CD, and the nice GUID-y looking value affixed to the PK name. Oh thank you.

Come on, Microsoft, help a brother out!!!  Harry actually submitted this to them (Microsoft) a few years ago, and they voted and decided to forego this fix I guess.

Thanks for reading,

Lee

-----------------------------

They were probably to busy building Notification Services.