Enforcing Constraints with CHECK and FOREIGN KEY

The most straightforward way to validate data is by using built-in constraints such as CHECK and FOREIGN KEY. These constraints are enforced at the database level and prevent invalid data from being inserted or updated.

Example: Using CHECK Constraint

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18)
);

In this example, the CHECK constraint ensures that only users aged 18 or older can be inserted into the Users table. Violating this constraint will result in an error.

Example: Using FOREIGN KEY Constraint

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

This FOREIGN KEY constraint ensures that every user_id in the Orders table must exist in the Users table, preventing orphaned records.

Constraint TypeUse CaseEnforced At
CHECKValidate column valuesDatabase
FOREIGN KEYEnsure referential integrityDatabase

Data Validation with Triggers

Triggers are powerful tools for executing custom logic in response to database events such as INSERT, UPDATE, or DELETE. They are especially useful when built-in constraints are insufficient.

Example: Preventing Negative Inventory

CREATE TRIGGER prevent_negative_stock
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Stock cannot be negative';
    END IF;
END;

This trigger prevents updates that would result in negative stock levels by raising an error before the row is updated.

Best Practices for Triggers

  • Use BEFORE triggers for validation logic.
  • Avoid complex logic that may impact performance.
  • Clearly document the purpose of each trigger.

Using Query Logic for Dynamic Validation

Sometimes validation logic depends on dynamic data or requires complex conditions that cannot be expressed in constraints. In these cases, validation logic can be embedded in queries using CASE, COALESCE, or conditional joins.

Example: Validating User Roles

SELECT *
FROM Users
WHERE role IN ('admin', 'editor', 'viewer');

This query ensures that only users with valid roles are retrieved, acting as a form of validation at the query level.

Example: Data Integrity Check in Query

SELECT order_id, customer_id
FROM Orders
WHERE customer_id NOT IN (SELECT customer_id FROM Customers);

This query identifies orders that reference non-existent customers, which could indicate data corruption or missing foreign keys.

Conditional Data Validation with CASE Expressions

The CASE expression allows you to embed conditional logic directly in SQL queries. It is especially useful when performing field-level validation or transforming data on the fly.

Example: Validating Email Format

SELECT user_id,
       CASE
           WHEN email LIKE '%@%.%' THEN 'Valid'
           ELSE 'Invalid'
       END AS email_status
FROM Users;

This query evaluates the email field and returns a status based on a simple pattern match. While not a full validation, it can be useful for quick checks.

Using Temporary Tables or CTEs for Validation

For more complex validation scenarios, temporary tables or Common Table Expressions (CTEs) can help organize and test data before committing changes.

Example: Validating Order Totals with a CTE

WITH OrderTotals AS (
    SELECT order_id, SUM(item_price * quantity) AS total
    FROM OrderItems
    GROUP BY order_id
)
SELECT o.order_id, o.customer_id, o.total, ot.total AS calculated_total
FROM Orders o
JOIN OrderTotals ot ON o.order_id = ot.order_id
WHERE o.total <> ot.total;

This query compares the stored total with a calculated total from the line items, identifying any discrepancies.

Error Handling with SIGNAL and RESIGNAL

In stored procedures and triggers, the SIGNAL statement allows you to raise custom errors when validation fails. This improves error messages and makes debugging more efficient.

Example: Using SIGNAL in a Stored Procedure

DELIMITER //
CREATE PROCEDURE validate_user(IN user_id INT)
BEGIN
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM Users WHERE user_id = user_id;
    IF user_count = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'User does not exist';
    END IF;
END //
DELIMITER ;

This stored procedure checks if a user exists and raises an error if they do not.

Learn more with useful resources