I have a table in my database called Table1. I need to retain all the data currently in Table1 (dropping the table isn't an option for me) and create a new, empty table with a slightly different structure that is also called Table1. To do this, I renamed my original Table1 to Table1_2015. Then I used the SQL Server Management Studio CREATE TO option to script my original table to a new query window, changed the fields I needed to change, and attempted to run the query to create the new Table1. SQL returns an error "Could not create constraint" (see below)
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'PK_Table1' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Here is my CREATE TABLE script:
/****** Object: Table [dbo].[Table1] Script Date: 03/15/2016 6:47:17 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Table1](
[dbID] [int] IDENTITY(200000,1) NOT NULL,
[PtID] AS ((CONVERT([varchar](4),datepart(year,GETDATE()))+'-')+CONVERT([varchar](20),[dbID])),
[ReceiptDate] [date] NULL,
[StockCode] [varchar](30) NULL,
[Supplier] [varchar](10) NULL,
[TareWt] [numeric](8, 2) NULL,
[GrossWt] [numeric](8, 2) NULL,
[NetWt] [numeric](8, 2) NULL,
[FLCount] [numeric](8, 2) NULL,
[SMCode] [varchar](40) NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
I think I understand that I'm getting this error because my original Table1, now renamed to Table1_2015, has a primary key constraint called PK_Table1. I can successfully create the table if I change the CONSTRAINT line to something different, such as [PK_Table1_New].
Where is this reference located? Is it in the sysobjects table (hidden on my system)?
When and how do the entries in this table get changed/deleted? Can I rename the constraint on the original table?
If I simply create my new table with a constraint name that does not match the table name, am I going to regret it?
Alternately, is there a better way to do this?