SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CREATE UNIQUE INDEX Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/05/2013 :  16:07:28  Show Profile  Reply with Quote
Hey guys,

I am trying to convert an Access Database Table to SQL Server but am getting the error below from SQL Server during the conversion:


SQL that Caused Error(s):
CREATE UNIQUE INDEX ID ON Transonic(ID, Date, Time)

Error(s):
Server Error 1505: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Transonic' and the index name 'ID'. The duplicate key value is 6.84621e+009, Aug 31 2010 12:00AM, Dec 30 1899 7:58PM).
Server Error 3621: The Statement has been terminated

I don't understand why the conversion is trying to execute that CREATE UNIQUE INDEX statement anyways because the ID columned is not indexed. The only column that is indexed in this table is the PK, which is RecID and I've already checked to make sure there are no duplicates. The ID, Date and Time fields are not indexed at all so I don't understand why this is occurring.

Thanks in advanced!
John

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/09/2013 :  00:56:22  Show Profile  Reply with Quote
the reason is there are already duplicate data existing in the table which is why it cant create the index. You can solve this in two ways

1. Remove duplicate data in table using the below sql


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ID,Date,Time) ORDER BY (SELECT 1)) AS Seq
FROM table
)t
WHERE Seq>1


and then create the index

2. create the index as a constraint with NOCHECK clause

ALTER TABLE Transonic WITH NOCHECK ADD CONSTRAINT constraintname UNIQUE (ID, Date, Time)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/09/2013 :  11:03:29  Show Profile  Reply with Quote
Thanks for the tip, but I still don't understand why SQL Server is trying to create this Index. Please see data below:

ID, Date, Time, RecID
1234, 2010-08-31, 7:58:00 PM, 36888
1234, 2010-08-31, 7:58:00 PM, 39808

If you only look at the ID, Date and Time columns, then yes, these are duplicates, but the Table Primary Key is the RecID field, which is unique so these are not duplicate rows. The Table design in MS Access does not have any Indexing options enabled for the ID, Date and Time field, so it does not make sense why SQL Server is doing this.

Thanks,
J
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  11:20:25  Show Profile  Visit russell's Homepage  Reply with Quote
Something is telling it to...

How are you doing this? Using the upsizing wizard in Access?

I'd use SSIS for this.

Edited by - russell on 04/09/2013 11:21:14
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/09/2013 :  11:32:55  Show Profile  Reply with Quote
Yes, I am using Upsizing Wizard in MS Access, but I can look into testing it with SSIS.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/09/2013 :  11:33:03  Show Profile  Reply with Quote
I don't see RecID in your index creation script:
CREATE UNIQUE INDEX ID ON Transonic(ID, Date, Time)
ID, Date, Time, RecID
1234, 2010-08-31, 7:58:00 PM, 36888
1234, 2010-08-31, 7:58:00 PM, 39808
If ID, Date, Time are not unique, then you are going to get a violation. Do you just what a regular index instead?

Edited by - Lamprey on 04/09/2013 11:34:25
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/09/2013 :  11:48:48  Show Profile  Reply with Quote
I did not write the Index creation script. SQL Server is doing this automatically, not sure why, because the Table Index is RecID. Like I said, ID, Date and Time fields are not indexed in MS Access so not sure where SQL Server is getting this information from.

Thanks all!
J
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/09/2013 :  12:02:25  Show Profile  Reply with Quote
Oh, I ws typing when you posted that the MS Access wizard is generating that for you. That is odd. Are there forign keys associated with those columns?
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/09/2013 :  13:48:24  Show Profile  Reply with Quote
Hey all,

The MS Access developer just got back to me and pointed out additinoal indexing options available in MS Access that I didn't know about. This is where SQL Server is getting the code. My apologies for your wasted efforts, but I am very appreciative for all of your time and support!

J
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/09/2013 :  14:35:22  Show Profile  Reply with Quote
hope you now got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 04/10/2013 :  08:04:04  Show Profile  Reply with Quote
Yes, thanks again!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000