RPG Native IO with SQL unique constraint

Hi,



I seem to be hitting a problem when using the RPG native I/O UPDATE with
a SQL table with a unique constraint. A table is set up as below:



OrderLine: PK(OrderID:LineID) : ProductID : Quantity



With a unique constraint across OrderID : ProductID : Quantity ie. Each
product in an order can have different quantity values but no two
records can have the same quantity.



Now imagine we have the values:



Record1: OrderID: 100001 LineID: 1 ProductID: DC1000
Quantity: 10

Record2: OrderID: 100001 LineID: 2 ProductID: DC1000
Quantity: 20

Record3: OrderID: 100001 LineID: 3 ProductID: DC1000
Quantity: 30



The user loads the table and makes the following changes:



Record1: OrderID: 100001 LineID: 1 ProductID: DC1000
Quantity: 15

Record2: OrderID: 100001 LineID: 2 ProductID: DC1000
Quantity: 30

Record3: OrderID: 100001 LineID: 3 ProductID: DC1000
Quantity: 45



Now before the update the table doesn't break the unique constraint and
if the entire update could complete it would again not break the unique
constraint. The problem is that when Record2 is updated it causes a
duplicate record error (as Record3 in the database as not been updated
yet and contains the same OrderID:ProductID:Quantity at this point). I'm
considering dropping the unqiue constraint at this point as the program
performs the checks on the data before update to ensure no duplicates
exist, but if possible I would like to keep the extra RI the unique
constraint provides.



Setting the primary key to OrderID:ProductID:Quantity is not an option
as each record has a timestamp field which is used to implement
optimistic concurrency, therefore each line has to be able to be
identified by non-changeable values (and good database design usually
indicates that primary key values should be limited to fields who's
values are generally rarely changed)



I would be interested to hear if anyone else has come across this while
using unique constraints?