• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 961
  • Last Modified:

unique constraint violation????

I hope i'm not missing someting totally obvious here.... i have a table, let's call it 'Orders'
there is a uniqe constraint in this table on EventTime,EventType,OrderID

I have these 2 records in the table right now:

EventTime                            EventType    OrderID
2007-10-03 08:55:59.317        O             03Vvufp7v
2007-10-03 08:55:59.367        A       03Vvufp7v      

I just tried to insert this one:
EventTime                            EventType    OrderID
'10/03/07 08:55:59.316'           'O'              '03Vvufp7v'

It fails w/the unique key constraint violation.
Server: Msg 2627, Level 14, State 2, Procedure procname, Line 32
Violation of UNIQUE KEY constraint 'uncName'. Cannot insert duplicate key in object 'Orders'.

That time is distinctly different from the existing data.  Hence, the three combined do not equate to what's already in the table.  

Why the violation?


0
dbaSQL
Asked:
dbaSQL
1 Solution
 
SQL_SERVER_DBACommented:
Im sure its the datatype your passing it as
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>That time is distinctly different from the existing data
unfortunately, ms sql server's time is only accurate up to 0.003 seconds.

just try to insert in a test table:
declare @t table ( v datetime )

insert into @t (v ) select convert(datetime, '2007-10-03 08:55:59.317', 120)
insert into @t (v ) select convert(datetime, '2007-10-03 08:55:59.316', 120)
insert into @t (v ) select convert(datetime, '2007-10-03 08:55:59.315', 120)
insert into @t (v ) select convert(datetime, '2007-10-03 08:55:59.314', 120)
insert into @t (v ) select convert(datetime, '2007-10-03 08:55:59.313', 120)

select * from @t
result:



v
2007-10-03 08:55:59.317
2007-10-03 08:55:59.317
2007-10-03 08:55:59.317
2007-10-03 08:55:59.313
2007-10-03 08:55:59.313

there is a rounding ... hence the unique constraint violated


0
 
ptjcbCommented:
Sorry about being obvious.
Are you sure that  'uncName' is the "unique constraint in this table on EventTime,EventType,OrderID"?

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DonKronosCommented:
If you run this

select cast('10/03/07 08:55:59.316' as datetime)

You get this

2007-10-03 08:55:59.317            

!!!!!

Looks like SQL Server rounds for some reason !?!?!???
0
 
dbaSQLAuthor Commented:
ptjch, no worries.  yes, i am certain
>>unfortunately, ms sql server's time is only accurate up to 0.003 seconds.
angell, that's exactly what i was worried about.  that damn time limitation has nailed me in many other places, too.

there must be a way around this?
0
 
dbaSQLAuthor Commented:
i'm not sure how to get around this at all.  
any suggestions?
0
 
dbaSQLAuthor Commented:
write the date as a varchar?  ugh.  gosh i hope not
i can't think of any other way around this.  
you guys got anything?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are only 2 ways:
* the varchar method
* use another field as pk
0
 
dbaSQLAuthor Commented:
unfortunately, the other field as pk isn't going to happen.  ( i already went down that road )
crap

i guess i was really just hoping....
thank you each for the feedback
0
 
dbaSQLAuthor Commented:
angelll, et al, i tried to get around this with an UPDATE IF EXISTS.  
see here:  http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22872652.html

any thoughts at all?  is it still not going to work?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now