How do you use a CHECK constraint to enforce a rule that spans several columns of the same row — for example that an end date is never earlier than a start date, or that a discounted price never exceeds the list price?

## Column-level versus table-level A CHECK can be written in two places. Inline, next to a column definition, it is a column constraint and is understood to constrain that column. As a separate comma-separated clause in the table definition, or added later with ALTER TABLE, it is a table constraint and may reference any columns of the row. Functionally engines are lenient about the inline form, but the conventional and portable rule is simple: **if the rule involves more than one column, write it at table level**. It also reads better — a reader scanning the column list is not surprised by a predicate that mentions a column defined thirty lines below. ## The evaluation model is unchanged A multi-column CHECK is still a per-row predicate evaluated against the candidate row version at write time. The important consequence is that an UPDATE touching only one of the involved columns still re-validates the constraint, because the engine tests the complete post-update image rather than the delta. So the pair cannot be broken by updating one half: shifting `starts_at` past a fixed `ends_at` fails just as an insert with a bad pair would. ## The three recurring shapes **Ordering between two values.** `CHECK (ends_at >= starts_at)`, `CHECK (min_qty <= max_qty)`, `CHECK (discount_price <= list_price)`. Decide deliberately whether equality is allowed — a zero-length interval is often a bug and often deliberate, and the constraint is where that decision becomes explicit and permanent. **Conditional presence — an implication.** 'If status is SHIPPED there must be a shipped_at.' In boolean terms `p implies q` is `NOT p OR q`, so the constraint is `CHECK (status <> 'SHIPPED' OR shipped_at IS NOT NULL)`. If you also want the reverse — shipped_at must be absent otherwise — state both directions, typically as a disjunction of the two legal shapes. Use IS NULL / IS NOT NULL rather than equality comparisons against NULL, which are always UNKNOWN. **Exactly-one-of / mutually exclusive columns.** A row must carry exactly one of `user_id` or `service_account_id`. Written directly: `CHECK ((user_id IS NULL) <> (service_account_id IS NULL))`, which is TRUE only when exactly one is populated — both comparisons are two-valued because IS NULL never returns UNKNOWN. Some teams prefer the more explicit form with an OR of the two legal combinations. Either is fine; the explicit form is easier for a reviewer to verify at a glance, the compact form is easier to extend badly, so prefer clarity. ## NULL handling is the whole game Every multi-column check must answer: what happens when one of the columns is NULL? The rule is unchanged — the row is rejected only when the predicate evaluates to FALSE, and a NULL operand in a comparison yields UNKNOWN, which passes. Consequences: - `CHECK (ends_at >= starts_at)` on an interval with a nullable `ends_at` correctly models 'still open'. Nothing extra is needed. - If both endpoints are mandatory, the answer is `NOT NULL` on both columns, and the CHECK stays a pure ordering rule. Do not write `CHECK (starts_at IS NOT NULL AND ends_at IS NOT NULL AND ends_at >= starts_at)` — it produces one confusing error for two different problems and hides nullability from anyone reading the column list. - If a mixed rule is genuinely intended — 'either both are present or neither is' — that is itself an exactly-one-of style predicate over IS NULL tests, and it belongs in the CHECK. ## Determinism still applies The temptation grows with multi-column rules: 'the end date must be in the future', 'the amount must not exceed today's limit'. Anything referring to the current time or to a value outside the row makes the constraint non-deterministic and therefore unsafe — the row was valid when written but a later revalidation, restore or table rewrite may reject it. Keep the predicate a pure function of the row's own columns. ## Naming and error handling Give each rule its own named constraint rather than bundling several unrelated conditions into one giant AND. Two reasons. First, the error message names the constraint, and `ck_booking_end_after_start` tells the on-call engineer what happened while `ck_booking_valid` does not. Second, application code that maps constraint names to user-facing messages needs one name per user-visible rule; a bundled predicate collapses several distinct messages into one. ## Where this rule shades into other mechanisms Multi-column CHECKs cover the row. The moment the rule needs a second row — 'these two bookings must not overlap' — it is no longer a CHECK problem; that is exclusion-constraint or index territory. Keeping that boundary clear is what stops a schema accumulating triggers that reimplement constraints badly.