The Case for Database Level Validations Even When APEX Already Validates

Defense in Depth

APEX provides excellent declarative and programmatic validation capabilities. So why bother adding constraints and triggers at the database level? Because your APEX application is not the only way data enters your database. SQL Developer sessions, data migrations, batch imports, REST APIs, and future applications can all modify data. Database level validations are the last line of defense, ensuring data integrity regardless of the entry point.

Check Constraints for Simple Rules

ALTER TABLE orders ADD CONSTRAINT chk_order_status
    CHECK (status IN ('DRAFT','SUBMITTED','APPROVED','SHIPPED','CANCELLED'));

ALTER TABLE employees ADD CONSTRAINT chk_salary_positive
    CHECK (salary > 0);

ALTER TABLE products ADD CONSTRAINT chk_price_range
    CHECK (unit_price BETWEEN 0.01 AND 999999.99);

NOT NULL and Unique Constraints

Never rely solely on APEX’s Value Required property. If a column must always have a value, enforce it with NOT NULL. If a value must be unique, a unique constraint prevents duplicates at the database level. APEX validations can check for uniqueness, but they are vulnerable to race conditions where two users submit simultaneously:

ALTER TABLE orders MODIFY customer_id NOT NULL;
ALTER TABLE employees ADD CONSTRAINT uk_employee_email UNIQUE (email);

Triggers for Complex Business Rules

CREATE OR REPLACE TRIGGER trg_validate_budget
BEFORE INSERT OR UPDATE ON projects
FOR EACH ROW
DECLARE
    l_dept_budget NUMBER;
    l_dept_spent  NUMBER;
BEGIN
    SELECT budget_limit INTO l_dept_budget
      FROM departments WHERE department_id = :NEW.department_id;

    SELECT NVL(SUM(budget), 0) INTO l_dept_spent
      FROM projects
     WHERE department_id = :NEW.department_id
       AND project_id != NVL(:NEW.project_id, -1);

    IF l_dept_spent + :NEW.budget > l_dept_budget THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Project budget exceeds department limit of ' ||
            TO_CHAR(l_dept_budget, 'FM$999,999'));
    END IF;
END;
/

Coordinating APEX and Database Validations

Use APEX validations for user-friendly, immediate feedback. Use database constraints for absolute data integrity. When a database constraint fires and APEX catches the ORA error, translate it into a user friendly message using an APEX error handling function rather than showing raw Oracle errors to the user.