Testing database constraints (2026)

TL;DR

Testing database constraints means proving that valid data commits at every allowed boundary, invalid data is rejected at the correct statement or transaction point, and failures leave storage and downstream behavior consistent. Cover NOT NULL, CHECK, UNIQUE, primary and foreign keys, referential actions, deferral, concurrency, rollout, and application error mapping.

Key Takeaways

• Inventory constraints from both the database catalog and business rules because names alone do not explain scope or timing.
• Pair every rejection test with valid boundary cases so an over-restrictive constraint cannot appear correct.
• Verify persisted state, transaction outcome, API mapping, and side effects after a database error.
• Test null, duplicate, collation, composite-key, referential-action, and deferred-transaction semantics explicitly.
• Use controlled concurrent transactions to prove the database, not an application precheck, resolves integrity races.
• Validate existing data before enforcing new constraints and test the rollout path used by production.
• Monitor lock duration, validation cost, rejected writes, and query plans when constraints change.

Testing database constraints verifies the final integrity boundary beneath application forms, API validation, imports, scripts, and concurrent workers. A constraint should admit every valid state, reject every state it forbids, behave correctly across transaction boundaries, and surface through the application without partial side effects or leaked database details.

This is not a checklist of invalid inserts. A useful strategy connects business invariants to actual catalog definitions, exercises null and comparison semantics, races concurrent transactions, validates referential actions, and tests how new constraints are introduced over existing data.

TL;DR

Test at two boundaries. Direct database tests prove engine semantics and schema deployment. API or service tests prove user-visible errors, transaction handling, and side effects. Neither layer replaces the other.

1. Treat Constraints as Executable Business Invariants

A database constraint is a rule the database enforces regardless of which approved writer submits the change. It protects against application bugs, race conditions, imports, old services, maintenance scripts, and future clients. Examples include 'an order line has a positive quantity,' 'an email is unique inside a tenant,' and 'an invoice item references an existing invoice.'

Start with the business sentence, then identify the database expression. A rule can be misimplemented even when a constraint exists. CHECK (end_at >= start_at) permits equal timestamps, while the requirement may demand a positive interval. UNIQUE (email) may be too broad for a multi-tenant product, or too narrow if case-insensitive uniqueness is required.

Classify the rule by scope:

• Column presence or domain.
• One-row relationship among fields.
• Uniqueness across rows.
• Parent-child referential integrity.
• Non-overlap or other multi-row operator rule.
• Cross-table or aggregate invariant, which may require a different design because ordinary CHECK constraints usually cannot reference arbitrary other rows.

Also define timing. Immediate constraints are checked at the statement boundary. Deferrable constraints can be checked at transaction end when explicitly deferred. Application validation may run before either, but it is a usability layer, not the final race-safe authority.

State the allowed set as carefully as the forbidden set. Over-restrictive constraints create production defects too. A negative-only suite might praise a rule that rejects all inserts. Pair every invalid case with valid values just below, at, and above important boundaries as appropriate.

2. Inventory the Schema Before Testing Database Constraints

Read migration files and query the database catalog in a disposable environment. Record table, columns, constraint name, type, expression, referenced table and columns, update and delete action, deferrability, validation state, and supporting index. Compare this inventory with data model documentation and API rules.

Create a coverage table:

Constraint names should be stable enough for operations and error mapping, but tests should not require autogenerated names that differ by environment. Prefer explicitly named constraints in schema code. The service can map known integrity categories or constraint names to safe domain errors without returning raw SQL or table details.

Look for gaps between application and database rules. A form may require displayName, while the database permits null. That may be deliberate for legacy records, but the decision should be explicit. Conversely, a database rule added for integrity may not have a friendly precheck, causing users to see a generic server error.

Prioritize money, identity, authorization, lifecycle, inventory, and relationships. Risk-based testing techniques help decide which invariants need concurrency and recovery depth.

3. Design a Constraint Test Harness and State Oracle

Use a disposable database created from the same migrations and engine version family as production. Run tests inside isolated schemas, containers, or rolled-back transactions when the behavior permits. Some cases need real commits, multiple connections, background consumers, or deferred checks, so transaction rollback cannot be the only cleanup strategy.

Build small named fixtures. tenantAlpha, invoiceOpen, invoicePaid, and missingCustomerId are easier to reason about than random rows. Generate unique namespaces for parallel runs. Seed parent rows before children and remove children before parents unless the test specifically covers referential action.

For each case, capture four outcomes:

1. Statement result and database error category or SQLSTATE.
2. Transaction state, including whether later statements or commit are possible.
3. Persisted rows, generated keys, sequences, and related records.
4. Application result, logs, metrics, events, cache changes, and notifications.

A failed SQL statement can abort the complete transaction in databases such as PostgreSQL until rollback or savepoint recovery. The application must not catch the exception and continue as if the transaction were healthy. Test a multi-step service operation where the constraint fails after an earlier write, then prove the whole intended unit rolled back.

Use direct SQL sparingly against shared environments. Destructive cases belong in isolated infrastructure. Test via the service for public behavior and via direct database connections for semantics the service cannot expose. Keep credentials least-privileged and never copy raw production rows into test artifacts.

4. Test NOT NULL, Defaults, and Generated Values

An omitted column, an explicit null, an empty string, whitespace, zero, and a missing JSON property are different inputs. A NOT NULL constraint rejects SQL null but does not reject empty text or zero. Application requirements may reject those through CHECK constraints or validation. Design cases that preserve the distinction.

Test an insert that omits a defaulted column and confirm the database supplies the intended value. Then explicitly insert null. In SQL, a default usually applies when the column is omitted or the DEFAULT keyword is used, not when null is provided. Verify update behavior too, because an existing non-null value may be set to null.

Defaults can depend on database time, sequence, identity, current user, or expression. Assert stable properties rather than exact unstable values. For a creation timestamp, confirm it lies within controlled transaction timing and uses the expected time zone type. For generated IDs, verify uniqueness and sequence state under explicit legacy inserts.

Generated columns should recompute from source fields according to engine behavior and reject direct assignment when not allowed. Test updates to each source field, null propagation, rounding, and overflow. If the application also calculates the value, compare it with database output and decide which is authoritative.

For add-column migrations, test old and new application versions. A new non-null column with a default can affect table rewrite, locking, and legacy insert behavior depending on database and change shape. Verify the actual rollout rather than assuming a generic engine rule. Testing data migrations covers backfill and cutover evidence.

A good negative assertion names the exact invariant: 'explicit null is rejected and no order or outbox event commits.' It is stronger than 'database throws error,' and it remains meaningful if error formatting changes.

5. Test CHECK Constraints and SQL Three-Valued Logic

A CHECK constraint accepts a row when its expression is true or unknown, and rejects it when false in common SQL databases such as PostgreSQL. Because comparisons with null evaluate to unknown, CHECK (price > 0) alone can allow null. Add NOT NULL when null is forbidden. This is one of the most important constraint semantics to test.

For numeric rules, cover exact boundaries, values immediately inside and outside, scale, rounding, maximum type range, and special floating values if the type supports them. Prefer exact numeric types for financial data. For text rules, cover empty, whitespace, length in characters and bytes, case, collation, normalization, and supported Unicode.

Cross-column checks need combination coverage. For discount <= subtotal, test both positive, equal, discount lower, discount higher, each null according to policy, and negative values protected by separate rules. Do not rely on one large expression when named smaller constraints give clearer diagnostics, unless atomic business semantics favor one rule.

Time rules need precision and zone awareness. Test equal instants expressed with different offsets, daylight-saving gaps and overlaps, fractional seconds, open-ended nulls, and start or end updates independently. Compare stored instants, not formatted strings, when the rule concerns chronology.

Enum-like checks require migration planning when a new value is introduced. Test that old application versions can read or safely ignore it during rolling deployment. A correct database acceptance can still break a deserializer. Conversely, adding the application value before the constraint changes produces rejected writes.

Keep volatile or environment-dependent logic out of a simple constraint unless the engine and design explicitly support it safely. A test should expose rules that depend on changing external state, such as current time or another table, because existing rows may become invalid without a write and the database may not reevaluate them as assumed.

6. Test UNIQUE, Primary, Composite, and Case Rules

A unique constraint defines a key and scope. Test the first insert, exact duplicate, duplicate after update, swap between two rows, delete then reuse, soft-delete behavior, and two concurrent inserts. For composite uniqueness, vary one component at a time and verify duplicates are rejected only when the complete scoped key conflicts.

Null semantics require explicit coverage. Many databases permit multiple null values under a conventional unique constraint because nulls are not equal for this purpose. Some engines or options support nulls-not-distinct behavior. Record the exact production definition and test it. Do not claim 'unique means only one null' universally.

Case and collation affect text uniqueness. User@example.com and user@example.com may compare as distinct or equal depending on type, collation, expression index, and normalization. The business rule may normalize only the domain part or may define case-insensitive usernames. Create examples for casing, accents, width, trailing spaces, and Unicode normalization that matter to supported users.

Primary keys combine uniqueness and non-null identity. Test generated and client-supplied values, collision, update policy, and foreign-key consumers. After importing explicit numeric IDs, verify the sequence or identity generator will not issue an already-used value.

Partial unique indexes often enforce rules such as one active subscription per customer while allowing historical inactive rows. Test rows inside and outside the predicate, transitions into and out of the active state, and concurrent activations. Because a partial unique index may not appear as a standard constraint in every catalog view, include indexes in the inventory.

Application prechecks improve messages but are race-prone. Two requests can both observe 'available' and then insert. The database constraint must decide the race, and the loser must receive a safe domain result. API error handling and negative testing helps validate that mapping.

7. Test Foreign Keys and Referential Actions

For an insert or update of a child reference, test an existing parent, missing parent, null when optional, wrong-tenant parent, soft-deleted parent according to business rules, and a parent removed concurrently. A foreign key enforces existence in the referenced key, but it does not automatically enforce tenant agreement or active status. Composite foreign keys or additional design may be needed.

Exercise every configured update and delete action:

Cascade tests must cover depth and fan-out. Deleting one parent may affect grandchildren, attachments, search rows, and audit policy. Count before and after, test rollback after a later failure, and verify caches or events represent the committed result. A cascade that removes required history may be structurally valid but violate retention rules.

Soft deletes are application state, not physical referential action. A foreign key still sees the row. Test whether new children can reference a soft-deleted parent and whether existing children remain usable. Enforce the business rule in an appropriate layer and include it in application tests.

Circular references and self-references need cases for root, child, deep chain, cycle attempt, delete, and deferred creation when supported. A foreign key alone does not prevent arbitrary cycles in a hierarchy. If acyclicity matters, test the mechanism that enforces it.

For cross-tenant systems, include tenant columns in the referential model or otherwise prove isolation. A valid global parent ID from another tenant can satisfy a simple foreign key while violating authorization and ownership.

8. Verify Immediate and Deferred Constraint Timing

Immediate constraints are checked at the end of each statement. A deferrable constraint can be postponed, commonly until transaction commit, when declared and set to deferred. This permits temporary inconsistency while a multi-step transaction reaches a valid final state, such as swapping unique positions or inserting cyclic references.

Test the default timing first. A constraint can be DEFERRABLE INITIALLY IMMEDIATE or DEFERRABLE INITIALLY DEFERRED. Begin a transaction, execute the temporarily invalid statement, and observe whether it fails immediately. Then explicitly set the constraint deferred and repeat.

Cover three transaction shapes:

1. Temporary violation repaired before commit, commit succeeds.
2. Temporary violation remains, commit fails.
3. Constraint is forced back to immediate before commit, failure occurs at that command.

After a failed commit, verify what the driver reports and how the application rolls back. Service code that expects every integrity error at the insert statement may mishandle a deferred failure arriving during commit. Logs and metrics need enough context to connect it to the business operation.

Savepoints can isolate expected failures in database tests. Verify the engine-specific transaction behavior instead of swallowing exceptions generically. A test suite that catches an error but leaves the connection in an aborted transaction will produce misleading failures later.

Do not defer rules without a real transaction need. Deferral expands the time during which reads inside the transaction may see an invalid intermediate state and moves failures farther from the causal statement. The test plan should cover that complexity and any worker or trigger that runs before commit.

9. Prove Constraint Behavior Under Concurrency

Concurrency is where application-only validation fails. Open two independent database connections and coordinate transactions with barriers. Both attempt the same unique key, both add a child while another transaction deletes the parent, or both activate a row under a partial unique rule. Make overlap observable with locks or test hooks rather than relying on timing luck.

For a unique race, assert that no more than one conflicting insert commits. The loser may block and then receive a unique violation, or fail through another documented concurrency mechanism. Do not require a specific winner unless ordering is part of the contract. Verify the transaction handles the failure and no duplicate event or external side effect escapes.

For foreign keys, test parent deletion against child insertion. Valid outcomes depend on isolation, statement order, and engine locking, but final committed state must satisfy the constraint. Record both transaction results and query final state from a third connection.

Deadlocks are not constraint violations, but constraint-related lock ordering can expose them. Create opposing operation orders in a controlled test, confirm the application recognizes the database's retryable transaction failure, and retry the whole transaction safely. Do not retry only the last SQL statement after the transaction has been rolled back.

Load can alter timing. Run a focused concurrency test before a broad performance test so the oracle is clear. Then exercise representative contention and measure wait time, abort rate, throughput, and retry success. Preserve database wait and lock diagnostics using safe object names and run IDs.

Keep external side effects behind a transactional outbox or another commit-aware design when appropriate. A database constraint can roll back rows but cannot unsend an email already emitted before commit. QA should test this failure ordering through the service boundary.

10. Test Application Error Mapping and Transaction Recovery

A public API should not return raw constraint names, SQL statements, driver stacks, or database hosts. It should map expected integrity failures to stable, safe domain errors. A duplicate username may be 409, an invalid positive quantity may be 422, and an internal unexpected integrity gap may be 500. Use the product contract rather than a universal status table.

Trigger each expected database category through the supported interface and inspect status, error code, field path, message safety, localization if applicable, correlation ID, and retry guidance. Then read the database to verify rollback. A friendly 409 is incorrect if the order row committed but its outbox row failed.

Different constraints can share one SQLSTATE category. The service may need the named constraint to distinguish duplicate email from duplicate external reference. Test known mappings and an unknown constraint fallback. The fallback should remain safe and observable rather than guessing a user-facing field.

Check logs and metrics. Internal evidence can contain constraint category, sanitized name, operation, service, and correlation ID, but should not include full payloads or personal data. Aggregate rejected-write metrics help identify client regressions or abuse, while excessive high-cardinality labels create operational problems.

Test recovery on pooled connections. After a violation, the transaction must roll back before the connection returns to the pool. Send a valid request afterward and verify it succeeds. A leaked aborted transaction can make unrelated users see confusing errors.

Client retries depend on category. A deterministic check or unique violation usually needs input or state change, not an immediate identical retry. A deadlock or serialization failure may be retryable as a whole transaction. Error contracts should not tell clients to retry permanent invalid data.

11. Introduce New Constraints Safely on Existing Data

A new constraint can fail because historical rows violate a rule that new application code already enforces. Profile before deployment and list violations by safe key and category. Decide whether to repair, quarantine, grandfather, or change the rule. Do not delete inconvenient records merely to make deployment green.

Use the database's supported staged validation mechanism when appropriate. For example, PostgreSQL can add certain constraints as NOT VALID, which enforces them for new or changed rows while postponing the scan of existing rows, then VALIDATE CONSTRAINT later. This does not apply identically to every constraint type or engine, so test the exact DDL.

Verify each rollout phase:

1. Old application with old schema.
2. Compatible schema added.
3. Old and new writers during rolling deployment.
4. Existing data repaired or backfilled.
5. Constraint validation completes.
6. Final application behavior and monitoring.
7. Cleanup of temporary compatibility logic.

Measure table locks, lock wait, validation duration, replication lag, transaction log growth, and application latency on production-like size. A logically correct constraint deployment can still violate availability objectives. Test cancellation or timeout of validation and confirm a safe rerun path.

After validation, query the catalog to prove the constraint is present, enabled, and validated on every intended database or partition. Run one direct positive and negative probe plus service regression. Deployment success text is not proof that every shard applied the change.

Coordinate schema changes with data migration testing techniques, especially when a backfill, default, or identifier remap precedes enforcement.

12. Automate Testing Database Constraints in PostgreSQL

The following self-contained PostgreSQL script creates temporary tables and proves NOT NULL, CHECK, UNIQUE, foreign-key, and deferred foreign-key behavior. Run it against a disposable PostgreSQL database with psql -v ON_ERROR_STOP=1 -f constraints.sql. Each nested block catches only the expected violation and raises an error if invalid data is accepted.

BEGIN;

CREATE TEMP TABLE account (
  account_id bigint PRIMARY KEY,
  tenant_id bigint NOT NULL,
  email text NOT NULL,
  balance_cents bigint NOT NULL,
  CONSTRAINT account_balance_nonnegative CHECK (balance_cents >= 0),
  CONSTRAINT account_email_per_tenant UNIQUE (tenant_id, email)
);

CREATE TEMP TABLE payment (
  payment_id bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  amount_cents bigint NOT NULL CHECK (amount_cents > 0),
  CONSTRAINT payment_account_fk
    FOREIGN KEY (account_id) REFERENCES account(account_id)
    DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO account VALUES (1, 10, 'one@example.test', 500);
INSERT INTO account VALUES (2, 20, 'one@example.test', 0);
INSERT INTO payment VALUES (100, 1, 25);

DO $test$
BEGIN
  BEGIN
    INSERT INTO account VALUES (3, 10, 'negative@example.test', -1);
    RAISE EXCEPTION 'expected check violation';
  EXCEPTION WHEN check_violation THEN
    NULL;
  END;
END
$test$;

DO $test$
BEGIN
  BEGIN
    INSERT INTO account VALUES (3, 10, 'one@example.test', 1);
    RAISE EXCEPTION 'expected scoped unique violation';
  EXCEPTION WHEN unique_violation THEN
    NULL;
  END;
END
$test$;

DO $test$
BEGIN
  BEGIN
    INSERT INTO payment VALUES (101, 999, 10);
    RAISE EXCEPTION 'expected foreign key violation';
  EXCEPTION WHEN foreign_key_violation THEN
    NULL;
  END;
END
$test$;

SET CONSTRAINTS payment_account_fk DEFERRED;
INSERT INTO payment VALUES (102, 3, 40);
INSERT INTO account VALUES (3, 10, 'late-parent@example.test', 40);
SET CONSTRAINTS payment_account_fk IMMEDIATE;

DO $test$
DECLARE
  account_count integer;
  payment_count integer;
BEGIN
  SELECT count(*) INTO account_count FROM account;
  SELECT count(*) INTO payment_count FROM payment;
  IF account_count <> 3 OR payment_count <> 2 THEN
    RAISE EXCEPTION 'unexpected final counts: accounts %, payments %', account_count, payment_count;
  END IF;
END
$test$;

ROLLBACK;

Add application-level tests that cause the same violations through supported commands and assert safe domain errors plus rollback. For concurrency, use two independent connections and explicit synchronization because one SQL script executes serially.

Interview Questions and Answers

Q: Why test database constraints when the API already validates input?

Application validation improves feedback but can be bypassed by other writers and cannot safely resolve every race. Database constraints provide the final invariant boundary, so I test both engine enforcement and user-visible service behavior.

Q: How do you test a CHECK constraint?

I derive partitions and exact boundaries from the expression and business rule, then test valid, false, and null-producing cases. I verify the statement or commit fails at the intended time and leaves no partial side effect.

Q: What is the null trap with CHECK?

In common SQL semantics, a CHECK rejects false but can accept unknown. Since a comparison with null is unknown, CHECK (amount > 0) does not necessarily forbid null. A NOT NULL rule is needed when absence is invalid.

Q: How do you test a unique constraint under concurrency?

I coordinate two transactions that insert the same scoped key and let the database resolve the conflict. I assert no more than one commits, the loser maps to a safe result, and final storage and events contain no duplicate effect.

Q: What foreign-key cases do you cover?

I cover valid, missing, optional null, update, delete, configured referential action, concurrency, tenant scope, soft-delete semantics, and deferral. I count all intended descendants for cascades and verify unrelated rows remain.

Q: What is a deferred constraint?

It is a constraint declared capable of being checked later and set to evaluate at transaction end rather than each statement. I test temporary violation repaired before commit, violation remaining at commit, and switching back to immediate timing.

Q: How do you test a new constraint on a large table?

I profile existing violations, rehearse the exact staged DDL on production-like volume, measure locks and lag, validate every deployment phase, and query the catalog afterward. I also test old and new application versions during rollout.

Q: What do you verify after a constraint error?

I verify database category, transaction rollback, unchanged persisted state, sequence or related effects as relevant, safe API mapping, logs, metrics, events, and that the pooled connection works for the next request.

Common Mistakes

• Repeating application validation tests without exercising the actual database rule.
• Testing only rejected values and missing valid boundaries.
• Treating empty text, zero, omitted input, default, and SQL null as identical.
• Assuming CHECK automatically rejects null.
• Assuming UNIQUE has universal null, case, collation, or normalization behavior.
• Testing foreign-key existence but skipping update, delete, cascade depth, and tenant meaning.
• Catching a database exception without rolling back the aborted transaction.
• Using an application availability precheck as protection against concurrent duplicates.
• Adding a constraint to historical data without profiling, rollout rehearsal, or lock measurement.
• Exposing raw constraint names and SQL details in public error responses.

Conclusion

Testing database constraints proves that the data store protects business invariants even when validation is bypassed, transactions overlap, or schema changes meet historical data. Inventory the exact definitions, pair valid and invalid boundaries, verify transaction and side-effect outcomes, and include concurrency and rollout evidence.

Select one critical rule, such as scoped identity or parent-child ownership. Trace it from business sentence to schema definition, direct SQL behavior, concurrent race, API error, and deployment plan. That vertical slice will reveal whether the constraint is a dependable control or merely an unchecked line of DDL.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Testing referential integrity (2026)
• Accessibility testing checklist (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• Agile testing quadrants (2026)