Put simple business rules in CHECK constraints

Simple business rules are cheap to enforce with CHECK, and they protect you from bugs in any code path, including one-off scripts and manual fixes that skip your app's validation.

Good candidates:

• "quantity is positive"
• "status is one of these values"
• "end date is after start date"

Keep checks to rules that only need the row itself. For rules that span rows, such as "no overlapping bookings":

• PostgreSQL 18 supports temporal keys with WITHOUT OVERLAPS, shown in the example.
• Exclusion constraints do the same job on older versions.

Name your constraints, such as order_items_qty_positive, so the error your code sees says which rule failed.

ALTER TABLE order_items
  ADD CONSTRAINT order_items_qty_positive CHECK (quantity > 0);

CREATE TABLE room_booking (
  room_id   int,
  valid_at  tstzrange,
  PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS)
);