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
 General SQL Server Forums
 New to SQL Server Programming
 CREATE UNIQUE INDEX Error

Author  Topic 

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-04-05 : 16:07:28
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

52326 Posts

Posted - 2013-04-09 : 00:56:22
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 - 2013-04-09 : 11:03:29
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

5072 Posts

Posted - 2013-04-09 : 11:20:25
Something is telling it to...

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

I'd use SSIS for this.
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-09 : 11:33:03
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?
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-04-09 : 11:48:48
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-09 : 12:02:25
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 - 2013-04-09 : 13:48:24
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

52326 Posts

Posted - 2013-04-09 : 14:35:22
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 - 2013-04-10 : 08:04:04
Yes, thanks again!
Go to Top of Page
   

- Advertisement -