it must pass a slightly more complex test than uniqueness:
- when inserted into PRICE_OFFERS table,
- the newly inserted object must have value in column PRICE, which is higher,
- than any (max) of the already existing objects in that table, which
- have YES in a boolean column VALID, and
- at the same moment, have same value in AUCTION_ID column as the inserted object.
(In fact the real condition is even more complex, but this is the gist of it: consider an auction system, where a new bid added to a particular auction must be higher than all previous valid bids for the same auction.)
Nevertheless, I believe that when we are pursuing the implement-the-behaviour-at-the-application-level way (unlike the check restraint at the DB level), the particular TEST is actually irrelevant. The gist is that it must not be possible to store an object
which does not pass TEST -- whatever the TEST tests.
Why is this a requirement? If highest price wins, then you only need to select the max price row where price offer date is less than auction end. If a few offers get thrown in there out of order, how does that break anything?