A CHECK constraint that caught a bad import

A constraint added for documentation, which then rejected eleven thousand rows from a supplier feed.

ALTER TABLE products ADD CONSTRAINT chk_price_sane CHECK (
  price_minor_units >= 0 AND price_minor_units < 100000000
);

-- the import: a supplier sending prices in major units
-- for one category, so £49.00 arrived as 49 rather
-- than 4900.
-- the constraint did not catch that. it caught the
-- category where a decimal separator was a comma and
-- the parse produced 4900000000.

The constraint caught the absurd case and not the plausible one, which is the general limit of a range check — an order-of-magnitude error inside the permitted range is indistinguishable from a real value. The plausible case was found by a report that compared the import against the previous week and flagged anything that moved by more than a factor of ten.