Database Reference
In-Depth Information
ERROR: new row for relation "fruits_in_stock"
violates check constraint
"fruits_in_stock_check"
DETAIL: Failing row contains (APPLE, 100, 115).
More interestingly, you also can't reserve more than you have, even though the con-
straints are on another table:
postgres=# UPDATE fruit_offer SET
offered_amount = 1100 WHERE offer_id = 1;
ERROR: new row for relation "fruits_in_stock"
violates check constraint
"fruits_in_stock_check"
DETAIL: Failing row contains (APPLE, 500,
1100).
CONTEXT: SQL statement "UPDATE fruits_in_stock
SET reserved = reserved -
OLD.offered_amount
+
NEW.offered_amount
WHERE name = NEW.fruit_name"
PL/pgSQL function reserve_stock_on_offer() line
8 at SQL statement
When you finally delete the offer, the reservation is released:
postgres=# DELETE FROM fruit_offer WHERE
offer_id = 1;
DELETE 1
postgres=# SELECT * FROM fruits_in_stock;
-[ RECORD 1 ]----
name | ORANGE
in_stock | 500
reserved | 0
-[ RECORD 2 ]----
name | APPLE
