Using CHECK Constraints in PostgreSQL: Enforce Business Logic

Previously in this course, we explored applying NOT NULL and UNIQUE constraints in PostgreSQL to ensure basic data structure integrity. While those constraints prevent missing or duplicate values, they don't help us enforce specific business rules, such as "a product price cannot be negative" or "inventory counts must be at least zero."

In this lesson, we add business logic directly to your database schema using CHECK constraints. This ensures that even if a bug creeps into your application code, your database will refuse to store invalid data.

What is a CHECK Constraint?

A CHECK constraint is a boolean expression that must evaluate to TRUE or UNKNOWN (for NULL values) for every row in a table. If you attempt to insert or update data that makes the expression evaluate to FALSE, PostgreSQL will reject the operation and throw an error.

Think of it as a gatekeeper. Instead of relying solely on your application code (which might be bypassed by other scripts or manual database updates), you embed the "rules of the game" directly into the table definition. This is the cornerstone of robust data integrity.

Implementing Business Logic: Price and Inventory

In our store project, we need to ensure that our product pricing and inventory levels remain sane. A negative price is a financial error; a negative inventory count is physically impossible.

Let’s apply these constraints to our products table.

Worked Example: Creating a Table with CHECK Constraints

We will use the CHECK keyword followed by an expression in parentheses.

SQL
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0)
);

In this example:

1. CHECK (price > 0) ensures that every product has a positive cost.
2. CHECK (stock_quantity >= 0) ensures we never accidentally log "negative stock" due to a calculation error.

If you try to execute INSERT INTO products (name, price, stock_quantity) VALUES ('Broken Widget', -10.00, 5);, PostgreSQL will return an error: new row for relation "products" violates check constraint "products_price_check".

Adding Constraints to Existing Tables

If you have already created your tables—as we did in earlier lessons like designing the products table—you don't need to delete them. You can use the ALTER TABLE command to add these rules retrospectively.

SQL
ALTER TABLE products
ADD CONSTRAINT check_positive_price CHECK (price > 0);

Using ADD CONSTRAINT allows you to name your constraint (in this case, check_positive_price), which makes debugging much easier when an error message points to a specific failed rule.

Practice Exercise

1. Open your terminal and connect to your store database.
2. Add a new table named discounts with columns discount_id, product_id, and percentage.
3. Apply a CHECK constraint to the percentage column to ensure the value is between 1 and 100 (inclusive).
4. Attempt to insert a record with a percentage of 150 to verify the constraint triggers an error.

Common Pitfalls

• Forgetting NULLs: Remember that CHECK constraints are not triggered by NULL values. If price is allowed to be NULL, a CHECK (price > 0) will effectively ignore the NULL row, treating it as valid. Always combine CHECK with NOT NULL if the column must contain a value.
• Performance Overhead: While CHECK constraints are very efficient, putting extremely complex logic (such as calling a custom function) inside a constraint can slow down INSERT and UPDATE operations. Keep them simple: basic mathematical comparisons and logical operators.
• Data Already in the Table: If you add a CHECK constraint to a table that already contains data violating that rule, PostgreSQL will throw an error and refuse to add the constraint. You must clean the data first.

FAQ

Can I use CHECK constraints to compare two columns? Yes! You can use CHECK (discounted_price < original_price) to ensure a sale price is always lower than the retail price.

Are CHECK constraints case-sensitive? When checking strings (e.g., CHECK (status IN ('active', 'inactive'))), the comparison is case-sensitive. 'Active' will fail if your check only allows 'active'.

Can I disable a CHECK constraint? You can, though it's rarely recommended in production. You can use ALTER TABLE products DISABLE CONSTRAINT ... to perform bulk imports, but be sure to re-enable it immediately after to maintain integrity.

Recap

In this lesson, we moved beyond basic data types to enforce business logic. By using CHECK constraints, we guaranteed that:

• Prices remain strictly positive.
• Inventory counts cannot drop below zero.
• Our database schema acts as the final source of truth for our business rules.

These constraints provide a safety net that protects your data from malformed inputs, no matter where the input originates.

Up next: We will continue our schema work by normalizing the store schema to ensure our data is stored in the most efficient and logical structure possible.