Duplicate Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Duplicate Key

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-14 : 18:02:43

I have just dropped the index for a table in my DB,and when i create newly it says as
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblone' and the index name 'PK_one'. The duplicate key value is (5635879).

How to resolve this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 18:16:24

You need to remove the duplicate(s) before adding a unique index. If you don't want it to be unique, then remove the word unique from the script.

In order for us to help you remove the duplicate(s), you'll need to show us on what column(s) the index is on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-14 : 18:42:29

Thanks Tara,
the index is on the columns [TCDID],ihave dropped and when iam creating it again it shows me the error.the below is table structure after deleting
USE [one]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[time](
[TCDID] [int] IDENTITY(1,1) NOT NULL,
[tdTimeCardID] [int] NOT NULL,
[tdProjectTaskID] [int] NULL,
[tdCostCenterID] [int] NULL,
[tdStatusID] [int] NULL,
[tSubmittalDate] [datetime] NULL CONSTRAINT [DF_time_tSubmittalDate] DEFAULT (getdate()),
[tdApprovalDate] [datetime] NULL,
[tdRejectedDate] [datetime] NULL,
[tdApproverID] [varchar](50) NULL,
[tdRejectedReasonID] [int] NULL,
[tdSaturdayHours] [float] NULL CONSTRAINT [DF_time_SaturdayHours1] DEFAULT (0),
[tdSundayHours] [float] NOT NULL CONSTRAINT [DF_time_SundayHours1] DEFAULT (0),
[tdMondayHours] [float] NOT NULL CONSTRAINT [DF_time_MondayHours] DEFAULT (0),
[tdTuesdayHours] [float] NOT NULL CONSTRAINT [DF_time_TuesdayHours] DEFAULT (0),
[tdWednesdayHours] [float] NOT NULL CONSTRAINT [DF_time_WednesdayHours] DEFAULT (0),
[tdThursdayHours] [float] NOT NULL CONSTRAINT [DF_time_ThursdayHours] DEFAULT (0),
[tdFridayHours] [float] NOT NULL CONSTRAINT [DF_time_FridayHours] DEFAULT (0),
[tdTimeCardRatesID] [int] NULL,
[tdNotes] [varchar](5000) NULL,
[tRowID] [int] NULL,
[tdCreateDate] [datetime] NULL CONSTRAINT [DF_time_tdCreateDate] DEFAULT (getdate()),
[tdSubmittedToVector] [datetime] NULL,
[tdAdjustedBy] [varchar](50) NULL,
[tdAdjustment] [int] NULL CONSTRAINT [DF_time_tdAdjustment_1] DEFAULT (0),
[tdParentTCDID] [int] NULL,
[tdSentToApproval] [datetime] NULL,
[tdSendtoCB] [datetime] NULL,
[tdSendtoJC] [datetime] NULL,
[tdGLProcessedDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[time] WITH CHECK ADD CONSTRAINT [FK_time_tblCostCenter] FOREIGN KEY([tdCostCenterID])
REFERENCES [dbo].[tblCostCenter] ([ccID])
GO
ALTER TABLE [dbo].[time] CHECK CONSTRAINT [FK_time_tblCostCenter]
GO
ALTER TABLE [dbo].[time] WITH CHECK ADD CONSTRAINT [FK_time_tblEmployee] FOREIGN KEY([tdApproverID])
REFERENCES [dbo].[tblEmployee] ([EmpID])
GO
ALTER TABLE [dbo].time] CHECK CONSTRAINT [FK_time_tblEmployee]
GO
ALTER TABLE [dbo].[time] WITH CHECK ADD CONSTRAINT [FK_ttime_tblProjectTask] FOREIGN KEY([tdProjectTaskID])
REFERENCES [dbo].[tblProjectTask] ([ProjectTaskID])
GO
ALTER TABLE [dbo].[time] CHECK CONSTRAINT [FK_time_tblProjectTask]
GO
ALTER TABLE [dbo].[time] WITH CHECK ADD CONSTRAINT [FK_time_tblTimeCard] FOREIGN KEY([tdTimeCardID])
REFERENCES [dbo].[tblTimeCard] ([TimeCardID])
GO
ALTER TABLE [dbo].[time] CHECK CONSTRAINT [FK_time_tblTimeCard]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 20:23:53

To see the duplicates, run this:

SELECT TCDID, COUNT(*)
FROM [time]
GROUP BY TCDID
HAVING COUNT(*) > 1

To get duplicates on an identity column, someone would have had to run SET IDENTITY_INSERT or DBCC CHECKIDENT.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 20:24:55

I'm confused by your error and your DDL though. The error has to do with tblone and not time. Are you sure you posted the right script or was trying to create the unique index on the correct column/table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 02:59:46

"To get duplicates on an identity column, someone would have had to run SET IDENTITY_INSERT or DBCC CHECKIDENT."

Blimey! I never knew that, I assumed SQL stepped over existing values (e.g. if you reset the CHECKIDENT back to 1).

No good reason for me to have assumed that though, of course ...


DROP TABLE #IdentityTest
GO
CREATE TABLE #IdentityTest
(
	MyID INT IDENTITY(1,1),
	MyValue VARCHAR(10)
)

-- Add rows
INSERT INTO #IdentityTest(MyValue)
SELECT 'AAA' UNION ALL
SELECT 'BBB'

-- Check the values:
SELECT * FROM #IdentityTest

-- Reset IDENTITY
DBCC CHECKIDENT ('#IdentityTest', RESEED, 0)

-- Add more rows
INSERT INTO #IdentityTest(MyValue)
SELECT 'CCC' UNION ALL
SELECT 'DDD'

-- Check the values again:
SELECT * FROM #IdentityTest

So always have a PK or UNIQUE index on IDENTITY columns ... (and GUID columns, whilst I'm at it ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources