Booking Rules as Constraints
Hard · Constraints
A CHECK constraint rejects any row for which its condition is false, on both INSERT and UPDATE. A condition can use several columns of the row: ALTER TABLE t ADD CONSTRAINT name CHECK (a < b);. "X is only allowed when Y" is written as CHECK (NOT X OR Y).
Add constraints to bookings so that:
1. check_out is after check_in (at least one night), and a stay is at most 30 nights.
2. status is one of 'pending', 'confirmed', 'cancelled'.
3. discount_pct is between 0 and 50.
4. A discount (discount_pct above 0) is only allowed on 'confirmed' bookings.
The existing rows follow all the rules. The check tries some inserts and updates and records which ones were accepted.