Data Validation

Definition

Data validation in the context of DML is the practice of ensuring data is correct and consistent BEFORE (or as part of) inserting/updating it — through a combination of database-level constraints (Chapter 10) and deliberate checks in the modification process itself.

How It Works

Validation happens at several layers, each catching different kinds of problems:

1. Database constraints (NOT NULL, CHECK, FOREIGN KEY, UNIQUE) — the strongest layer, enforced by the engine itself regardless of which application or script writes the data.
2. Application-level validation — business logic checks before ever sending SQL to the database (e.g. validating an email format, checking a business rule that spans multiple unrelated tables that a single CHECK constraint can't express).
3. Post-modification verification queries — running a SELECT/COUNT after a bulk change to confirm the expected number of rows were affected, or that no unexpected NULLs or out-of-range values appeared.

sql
-- Post-load sanity check: are there any salaries that snuck in as non-positive?
SELECT COUNT(*) FROM employees WHERE salary <= 0;

Edge Cases and Pitfalls

• Relying ONLY on application-level validation (skipping database constraints entirely) is risky: any future script, migration, admin tool, or bug that writes to the table directly bypasses application logic entirely, and only database-level constraints protect against that.
• Relying ONLY on database constraints without any application-level validation can produce a poor user experience — a raw constraint-violation error is often far less helpful to an end user than a clear, specific application-level validation message shown before the data is even submitted.
• Data imported from an external, untrusted, or historically-inconsistent source (bulk loads, legacy system migrations) especially benefits from explicit post-load verification queries, since bulk-load paths (11.9) can validate less strictly by default.

Key Takeaways

• Data validation is layered: database constraints (strongest, universal), application-level checks (best user experience), and post-modification verification queries (catches what slipped through).
• Skipping database-level constraints in favor of application-only validation leaves data vulnerable to any future direct-write path.
• Bulk-loaded or migrated data specifically benefits from explicit post-load sanity-check queries.