SQL Primary Key Exception

Question

I have a Mircrosoft Sql Server database made up of about 8 tables that are all related that I am trying to update. To do this I create a number of temporary tables

"CREATE TABLE [vehicle_data].[dbo].[temp_MAINTENANCE_EVENT] (" +
                       "[maintenance_event_id] int," +
                       "[maintenance_computer_code_id] int," +
                       "[veh_eng_maintenance_id] int," +
                       "CONSTRAINT [PK_maintenance_event_id"] PRIMARY KEY CLUSTERED ([maintenance_event_id] ASC))";

Then after all the temporary tables have been created I drop the existing tables, rename the temporary tables, and add foreign keys and indexing to the new tables to speed up joins and querying.

The issue I'm having is the original primary key references are remaining. So when I go to update again I get

Exception: There is already an object named 'PK_maintenance_event_id' in the database. Could not create constraint.

I'm wondering what is the best course of action is? Should I not set the primary key when I create the temporary table and instead add it to the table after it has been renamed? Or is there a way to rename constraints so that when I rename the table I can change the name of the primary key constraint.

After the original tables have been dropped I want there to be as little downtime as possible, but anything that happens before the tables have been dropped can take a really long time and it won't matter.

Do you need to keep the PK name? If not, then just don't name it and SqlServer will make up a unique name for it. — RBarryYoung, May 08 '15 at 17:36
Ha! Just noticed, the guy in the big red Ad to the right is a friend of mine. Nice. :-) — RBarryYoung, May 08 '15 at 17:37
I don't see the Ad haha but any idea on the syntax for that? — nastassiar, May 08 '15 at 17:39
Oops, OK, I found it. You just remove the `CONSTRAINT [name..]` part of your current syntax. — RBarryYoung, May 08 '15 at 17:43
Ok awesome thank you! I tried to get rid of the name before by just leaving out the name and leaving constraint and couldn't get the syntax to work. — nastassiar, May 08 '15 at 17:45
@RBarryYoung I assume you must have seen the one with Grant in it? :D — Sean Lange, May 08 '15 at 18:39

score 3 · Accepted Answer · answered May 08 '15 at 17:57

If your temp tables need that constraint

When create use

CONSTRAINT [PK_maintenance_event_id_temp"]

instead of

CONSTRAINT [PK_maintenance_event_id]

when you rename temp back to real table

exec sp_rename [PK_maintenance_event_id_temp], [PK_maintenance_event_id]

SQL Primary Key Exception

1 Answers1

Linked