Data Integrity via Constraints and Triggers

You will combine declarative constraints with procedural triggers for complex data integrity rules.

In this lesson, you will learn how to architect robust data integrity systems by layering declarative constraints with the surgical precision of procedural triggers.

The Hybrid Strategy

While declarative constraints (like CHECK, UNIQUE, or FOREIGN KEY) are the first line of defense for data integrity, they are inherently limited to static, row-level conditions. When your business logic requires cross-row validation, historical context, or complex conditional dependency, you must pivot to triggers. By combining these, you create a "defense-in-depth" architecture: constraints handle the simple, fast rules, while triggers manage the complex, stateful ones.

Practical Implementation

Consider a project_allocations table where you want to ensure that no single employee is allocated to projects exceeding a total "effort" of 100%. A declarative constraint cannot look at the sum of other rows, so we use a trigger.

-- Declarative constraint for basic sanity
ALTER TABLE project_allocations 
ADD CONSTRAINT positive_effort CHECK (effort > 0);

-- Trigger function for complex cross-row validation
CREATE OR REPLACE FUNCTION check_total_effort() RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT SUM(effort) FROM project_allocations WHERE emp_id = NEW.emp_id) > 100 THEN
    RAISE EXCEPTION 'Employee % exceeds 100%% total effort', NEW.emp_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_verify_effort
AFTER INSERT OR UPDATE ON project_allocations
FOR EACH ROW EXECUTE FUNCTION check_total_effort();

Why Both?

Using only a trigger would be inefficient; the database would have to verify basic data types and simple positive values through complex procedural code, which is slower than native constraint checks. By keeping CHECK (effort > 0) on the column, you filter out invalid data before the trigger is even invoked. The trigger then acts as the "intelligence" layer, managing the relational state across rows. This approach balances raw performance with the flexibility required for nuanced business requirements.

Key Takeaway: Use declarative constraints for simple, row-local validation to maintain high performance, and reserve triggers for complex logic that requires context beyond the current row.