SQL Primary Key Exception

I have a Mircrosoft Sql Server database made up of 8 tables, which are all related to what I am trying to update. For this I create some 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 temp tables have been created, I drop the existing tables, rename the temp tables, and add foreign keys and indexing to the new tables to speed up joins and queries.

The problem I ran into was the original primary key references. So when I update again I get

Exception: An object named "PK_maintenance_event_id" already exists in the database. Failed to create constraint.

I am wondering what is the best course of action? Should I not be setting the primary key when I create a temporary table and instead add it to the table after renaming it? Or is there a way to rename the 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 whatever happens before the tables have been dropped can take a very long time and it doesn't matter.

+3


source to share


1 answer


If your temporary tables need this constraint

When creating, use

CONSTRAINT [PK_maintenance_event_id_temp"]

      

instead



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]

      

+3


source







All Articles