SQL Create table fails with error:  Could not create constraint

monkeybiz12345
monkeybiz12345 used Ask the Experts™
on
Greetings!

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:
USE [DBTEST]
GO

/****** Object:  Table [dbo].[Table1]    Script Date: 03/15/2016 6:47:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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 
(
	[dbID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


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?  

Many thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013

Commented:
I recommend you use sp_rename for renaming the objects in SQL Server. Do not attempt to update the system tables.

To rename the index use:
sp_rename 'PK_Table1', 'PK_Table1', 'INDEX'

Open in new window

or
sp_rename 'PK_Table1', 'PK_Table1', 'OBJECT'

Open in new window

or
sp_rename 'PK_Table1', 'PK_Table1'

Open in new window

Right click the table and expand indexes and constraints. You can't have any duplicates, but you can rename at will.
You can avoid all this by creating the new table first (with a different name) and later renaming both as necessary.
Commented:
@Where is this reference located?  Is it in the sysobjects table (hidden on my system)?
   You can check it by expanding table referring screen shot.
PK_Rename.JPG
@When and how do the entries in this table get changed/deleted?  Can I rename the constraint on the original table?

    Yes you can do it as showing in screen shot , Right click on the PK or index and rename.

@If I simply create my new table with a constraint name that does not match the table name, am I going to regret it?

   No issue if you create a constraint with different name

@Alternately, is there a better way to do this?

Well.... you can add year with constraint name like "PK_Table1_2016"
Or you can rename existing or create new one.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jason clarkDBA Freelancer

Commented:
1. You should try to insert to the same temporary table from different connections (which is impossible, instead of global temp tables).
2. or you try to insert into different tables.

If 2nd - you simply may do the following - ALTER TABLE #temp ADD PRIMARY KEY(...)

If 1st - you have to create the table (regular or global temporary) with key prior to use it in parallel operations
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Before running the script to create the new Table1, recreate the PK for the old one (Table1_2015):
ALTER TABLE Table1_2015
DROP CONSTRAINT PK_Table1
GO
ALTER TABLE Table1_2015
ADD CONSTRAINT PK_Table1_2015 PRIMARY KEY CLUSTERED (dbID);
GO

Open in new window

Now you're able to run the script to create the new table since the PK_Table1 reference has gone.

Author

Commented:
Sorry I disappeared for a few days.... got pulled into other people's emergencies and just resurfaced today.

This is exactly what I wanted to know. Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial