monkeybiz12345
asked on
SQL Create table fails with error: Could not create constraint
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:
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!
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
GOI 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!
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.
You can avoid all this by creating the new table first (with a different name) and later renaming both as necessary.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
This is exactly what I wanted to know. Thank you!
To rename the index use:
Open in new window
orOpen in new window
orOpen in new window