Business Constraints

Definition

Business constraints (also called semantic or domain-specific constraints) are integrity rules that come from the real-world rules of the business itself, rather than from the structural relational-model rules covered elsewhere in this chapter (entity integrity, referential integrity). SQL's CHECK constraint is the main mechanism used to enforce them directly in the schema.

How It Works

sql
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  salary INT CHECK (salary > 0),
  hire_date DATE,
  termination_date DATE,
  CHECK (termination_date IS NULL OR termination_date >= hire_date)
);

Neither of these rules comes from the relational model itself — nothing about "being a valid relation" requires salary to be positive or termination to come after hiring. They come from the business domain: a negative salary is nonsensical, and someone can't be terminated before they were hired. CHECK constraints let the database itself refuse to store data that violates these business-specific rules, rather than relying entirely on application code to catch them (which every calling application would then need to duplicate correctly).

Edge Cases and Pitfalls

• Business rules are the most likely category of constraint to CHANGE over time as the actual business changes (e.g. a minimum-salary rule changing when regulations change) — unlike entity/referential integrity, which are stable, timeless properties of the relational model itself.
• Overly strict CHECK constraints can block legitimate edge cases the designer didn't anticipate (e.g. a CHECK (age >= 18) on a "customer" table breaks the moment the business decides to support minors with a guardian) — business constraints need periodic review, not "set once and forget."
• Complex business rules that span MULTIPLE rows or tables (e.g. "the total of all order line items must equal the order's stated total") generally can't be expressed as a simple single-row CHECK constraint — those require triggers (covered in Chapter 30) or careful application-level enforcement instead.

Key Takeaways

• Business constraints encode real-world domain rules, not structural relational-model requirements.
• CHECK is SQL's direct mechanism for enforcing single-row business rules.
• Business rules change more often than structural integrity rules, and some (multi-row rules) need triggers rather than a plain CHECK.