Database Constraints Refinement: Multi-Column Logic in PostgreSQL

Previously in this course, we explored using CHECK constraints in PostgreSQL: Enforce Business Logic to validate single columns. In this lesson, we build on those fundamentals by implementing table-level constraints that evaluate multiple columns simultaneously, ensuring your store's data integrity is ironclad even when logic spans across different fields.

Why Move Beyond Column-Level Constraints?

While column-level constraints (like CHECK (price > 0)) are perfect for simple field validation, they fail when your business logic depends on the relationship between two or more values.

For instance, in our store project, we might need to ensure that a "discounted_price" is always lower than the "original_price," or that a "sale_end_date" occurs strictly after a "sale_start_date." These rules require the database to look at the row as a complete entity.

Implementing Multi-Column Constraints

When you define a constraint at the table level, the database evaluates the condition only after all columns in the expression are available. You define these within the CREATE TABLE statement or by using ALTER TABLE.

Worked Example: Validating Product Sales

Let's refine our products table. We want to ensure that if a product is marked as "on sale," the sale_price must be strictly less than the regular_price.

SQL
ALTER TABLE products 
ADD CONSTRAINT check_sale_price_logic 
CHECK (
    NOT is_on_sale OR (sale_price < regular_price)
);

In this constraint:

1. NOT is_on_sale: If the product isn't on sale, the condition is true (the check passes).
2. OR (sale_price < regular_price): If it is on sale, the second part must be true.

This prevents the logical error of having a "discounted" price that is actually more expensive than the original price.

Hands-on Exercise: Enforcing Date Ranges

In your orders or a hypothetical promotions table, apply a constraint that ensures a start date cannot be later than an end date.

Task:

1. Create a promotions table with columns start_date and end_date (both DATE types).
2. Add a table-level CHECK constraint that enforces start_date <= end_date.
3. Attempt to insert a row where the start date is in the future relative to the end date and verify that PostgreSQL rejects it.

Hint: Use the syntax CONSTRAINT constraint_name CHECK (column_a <= column_b).

Common Pitfalls to Avoid

• Ignoring NULLs: In SQL, if any column in a CHECK expression is NULL, the result of the expression is NULL (which is treated as "not true," meaning the row is rejected). If you have optional columns, use COALESCE or IS NULL checks inside your constraint to avoid accidentally blocking valid rows.
• Performance Overhead: While CHECK constraints are very fast, they are evaluated on every INSERT and UPDATE. Avoid overly complex functions or subqueries inside constraints, as these can significantly slow down write operations.
• Renaming Confusion: Always name your constraints (e.g., CONSTRAINT valid_dates CHECK (...)). If you don't, PostgreSQL will assign an auto-generated name like products_check1, which makes debugging error messages much harder when a constraint fails.

FAQ

Can I use subqueries in a CHECK constraint? No. PostgreSQL requires CHECK constraints to be immutable—they must return the same result given the same input without querying other tables. Use a TRIGGER if you need to validate data against another table.

How do I modify a constraint once it is set? You must DROP the existing constraint first using ALTER TABLE table_name DROP CONSTRAINT constraint_name, then ADD the new one.

Recap

We have moved from basic column validation to sophisticated table-level logic. By using multi-column CHECK constraints, we ensure that our data integrity remains consistent regardless of how complex our business rules become. This approach, paired with the techniques discussed in constraints and data integrity: A guide to SQL DDL, keeps our store schema robust and reliable.

Up next: We will look at how to audit your indexes and remove unused ones in our performance tuning masterclass.