As a quick follow up...
For anyone interested I've come across the following which provides a
means around the issue without deferred constraints. It basically
involves adding an isTransient flag field to the table, and making this
part of the unique key. When a field that is part of the unique key is
changed the isTransient field is set to 1 (or true). Once all the
changes have been made your save procedure will set all the saved
record's isTransient fields to 0 (or false), then save the records again
at which point the unique constraint will be applied to all records in
the table. It works but obviously doubles the amount of writes to the
DB. Here is the discussion:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/t
hread/76d3ade4-38a3-4205-8c98-260e6ce7b217
As for me I think I'll just be turning those constraints off;)
Cheers,
Steve
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Steven Harrison
Sent: Wednesday, 9 September 2009 10:45 AM
To: RPG programming on the IBM i / System i
Subject: RE: RPG Native IO with SQL unique constraint
Hi Scott,
In Oracle and a number of other SQL implementations (and I believe it's
part of the SQL specification although I'd have to check that?) you can
specify deferred constraints that opposed to being checked after every
SQL statement are checked after a SQL transaction...
This is to allow for instances like I have mentioned. It's perfectly
reasonable to assume that there may be batches of statements that at
some point during a transaction the individual statements may break a
constraint but if you're treating the transaction as an atomic process
then the first and final state can still be correct. It's not something
that is used much but it can sometimes be useful.
In the example of the quote, it's a valid business rule that each quote
line is unique for a product ID and quantity. But if for example you
want to implement optimistic concurrency control using a timestamp
(which requires a "last updated" timestamp field) then the following can
occur:
Initial State:
QuoteID QuoteLineID ProductID Quantity Last
Update
10000 1 Product1 15
"Timestamp Value"
10000 2 Product1 30
"Timestamp Value"
User 1 loads the screen and performs their edits. (To enforce optimistic
concurrency control it is important that the primary key values are
static so then if user 2 loads the same screen, maybe changes line 1 to
Product2, then when the user 1 attempts to save the above state the
system can flag that changes have been made since the last save and not
overwrite user 2's changes) Anyway, say user 1 enters:
QuoteID QuoteLineID ProductID Quantity Last
Update
10000 1 Product1 30
"Timestamp Value"
10000 2 Product1 50
"Timestamp Value"
10000 3 Product1 70
NULL (new line)
The above data satisfies the business rule but the process of writing
the data to the database will result in violating the unique constraint
unless you treat the entire update as a transaction. Sure the user could
change the first line to a quantity of 50 instead and leave the 2nd line
with a value of 30 but this puts a lot of limitations on what the user
can and can't do...not a good thing at all.
I haven't had much exposure to iSeries so I don't know if deferred
constraints are implemented in it's version of SQL but I'm pretty
positive nothing of the sort exists in native IO.
So SQL doesn't really let you ignore the constraint but just defer the
check. I'm not sure how you would implement optimistic concurrency
control with data with unique constraints otherwise?
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Wednesday, 9 September 2009 10:05 AM
To: RPG programming on the IBM i / System i
Subject: Re: RPG Native IO with SQL unique constraint
Hello,
Regardless, the main issue is native IO with unique constraints.
I'm confused. Are you saying (going back to your original example) that
SQL will let you set the 2nd record to 30 while the 3rd record is
already 30?!
I wouldn't think so!
Therefore, I'm looking at this saying to myself that it's not a question
of Native I/O with constraints. It's a question of having a constraint
that makes sense for your business rules. (And you don't seem to have
that.)
Or does SQL really allow you to ignore the constraint?
midrange.com
