BVA and Equivalence Partitioning for Database Testing: Schema, Queries, and Constraints

Database testing has its own equivalence partition structure driven by database constraints, SQL semantics, and the specific behaviors of NULL, type boundaries, and referential integrity.

This post covers how to apply BVA and EP to database layer testing specifically.

Database Column Constraints as Partition Sources

Every column constraint defines partitions:

NOT NULL constraints

For a column with NOT NULL:

The empty string partition is frequently missed. In SQL, empty string and NULL are different — a NOT NULL column can hold an empty string. Depending on business requirements, empty string may be valid or may require a separate CHECK constraint.

VARCHAR length constraints

For name VARCHAR(100):

The multibyte character partition is important for internationalized applications. In UTF-8, VARCHAR(100) means 100 characters in PostgreSQL but 100 bytes in some MySQL configurations. A string of 100 two-byte characters may fail in one database but succeed in another.

Numeric precision and scale

For DECIMAL(10, 2) (10 total digits, 2 after decimal):

The scale truncation behavior varies by database: PostgreSQL rounds, MySQL rounds or truncates depending on SQL mode, Oracle raises an error. Testing with boundary precision values catches these database-specific behaviors.

CHECK constraints

For CHECK (age BETWEEN 0 AND 150):

Check constraints are often added in migrations and tested only at the application layer. Direct database testing verifies the constraint fires correctly even when the application bypasses it (direct SQL, batch imports, other applications sharing the database).

NULL Partitions in SQL Queries

NULL has three-valued logic in SQL (true/false/null). Queries that don't account for NULL produce incorrect results — a class of bugs that EP for NULL specifically targets.

NULL in WHERE clauses

For SELECT * FROM users WHERE department_id = ?:

A query using = NULL instead of IS NULL silently returns zero rows. This is a correctness bug, not a crash bug, and is easily missed.

NULL in aggregate functions

Aggregate behavior with NULL is another EP class:

For SELECT COUNT(*), COUNT(field), AVG(field), SUM(field), MIN(field), MAX(field):

The all-null and empty-table partitions are the boundary conditions. Queries that divide by COUNT or that compare the result directly to a number without NULL-handling crash or produce wrong results in these partitions.

NULL in JOINs

For INNER JOIN, LEFT JOIN, RIGHT JOIN with NULL foreign keys:

Testing LEFT JOIN with NULL FK values verifies orphan detection queries work correctly. An analytics query that should count orphaned records fails silently if NULL FK handling is wrong.

Query Result Partitions

When testing queries directly (rather than through the application), apply EP to the result set.

Pagination query partitions

For SELECT ... LIMIT ? OFFSET ?:

OFFSET beyond the end of results should return an empty result set, not an error. Some ORMs and query builders return errors or incorrect results when OFFSET exceeds total count.

Ordering edge cases

For SELECT ... ORDER BY created_at ASC:

NULL ordering in SQL is database-specific: in PostgreSQL, NULLs sort last for ASC by default; in MySQL, NULLs sort first. If the application relies on consistent ordering of NULLs without explicit NULLS FIRST/LAST, behavior differs across databases.

Referential Integrity Partitions

For tables with foreign key relationships:

INSERT with FK constraint

For orders(user_id REFERENCES users(id)):

DELETE with dependent rows

For deleting a user with existing orders:

Test each FK action explicitly. A schema defined with RESTRICT but intended to cascade is a data integrity bug.

Transaction and Concurrency Partitions

Isolation level partitions

For concurrent transactions, the isolation level determines what each transaction sees:

Test each isolation level against the specific phenomena it should prevent:

Dirty read test (at READ UNCOMMITTED):

1. Transaction A begins, updates row
2. Transaction B reads the same row (sees uncommitted value — dirty read)
3. Transaction A rollbacks

Non-repeatable read test (at READ COMMITTED):

1. Transaction A begins, reads row → gets value X
2. Transaction B updates and commits the row → value becomes Y
3. Transaction A reads the same row again → gets value Y (non-repeatable)

Concurrency boundary partitions

For operations that must be atomic:

Deadlock tests require timing control — deliberately constructing the interleaving that produces the deadlock. In practice, run the concurrent operations with a barrier to synchronize them at the conflict point.

Database-Specific Boundary Values

Different databases have different type boundary values that matter for partition analysis:

PostgreSQL

Test overflow: inserting 2147483648 into an INTEGER column should produce an overflow error, not silent truncation to a wrong value.

MySQL

MySQL has STRICT_TRANS_TABLES mode. Without strict mode, out-of-range values are silently clamped. With strict mode, they produce errors. Testing in both modes reveals whether application code depends on silent truncation.

Summary

Database layer EP follows the same structure as application layer EP: identify the behavioral regions, test boundaries and transitions. The database-specific additions: column constraint partitions, three-valued NULL logic, referential integrity states, and transaction isolation levels.

Direct database testing is valuable even when application-level tests exist. Applications can bypass constraints (direct SQL, batch imports), ORM mappings can have bugs, and database vendor differences create behavior that only direct testing reveals.

For each database constraint, write tests that exercise both the passing and failing side. For each query, test the NULL and empty-result partitions explicitly. For concurrent operations, test the boundary between success and conflict.