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,OrderI
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?
there is a uniqe constraint in this table on EventTime,EventType,OrderI
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?
SQL_SERVER_DBA
Im sure its the datatype your passing it as
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry about being obvious.
Are you sure that 'uncName' is the "unique constraint in this table on EventTime,EventType,OrderI
Are you sure that 'uncName' is the "unique constraint in this table on EventTime,EventType,OrderI
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 !?!?!???
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 !?!?!???
ASKER
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?
>>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?
ASKER
i'm not sure how to get around this at all.
any suggestions?
any suggestions?
ASKER
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?
i can't think of any other way around this.
you guys got anything?
there are only 2 ways:
* the varchar method
* use another field as pk
* the varchar method
* use another field as pk
ASKER
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
crap
i guess i was really just hoping....
thank you each for the feedback
ASKER
angelll, et al, i tried to get around this with an UPDATE IF EXISTS.
see here: https://www.experts-exchange.com/questions/22872652/UPDATE-IF-EXISTS-otherwise-INSERT.html
any thoughts at all? is it still not going to work?
see here: https://www.experts-exchange.com/questions/22872652/UPDATE-IF-EXISTS-otherwise-INSERT.html
any thoughts at all? is it still not going to work?