Constraints, Transactions, and Rollback

Test schema invariants directly

Test that NOT NULL, unique, check, and foreign-key constraints enforce the business invariants they are meant to protect. Assert the intended SQLSTATE or application error contract; avoid matching a localized or version-specific full error string.

-- Example: a duplicate key must be rejected.
INSERT INTO customers (customer_id, name)
VALUES (9101, 'First');

-- Test harness attempts a second row with customer_id=9101.
-- Assert a unique-violation error (PostgreSQL SQLSTATE 23505).
-- Then verify the original row remains unchanged.

Test cascade/restrict behavior explicitly. A cascade is a data deletion contract and should never be assumed safe simply because a foreign key exists.

Test atomic multi-step work

A transaction should leave all related rows committed together or none of them committed when a step fails. Test both outcomes using the real engine and data-access code.

BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 25 WHERE account_id = 2;
-- Assert both balances, then COMMIT in the success case.
-- In a separate failure-path test, force a known failure and ROLLBACK.
ROLLBACK;

This is only a simplified example. In an application, use parameterized statements and test the actual transaction boundary, including exception handling and connection reuse.

Isolation and test cleanup

• Use a per-test transaction and rollback when all operations remain on the same connection and the code supports it.
• Do not assume rollback undoes external side effects, other connections, sequence increments, or operations prohibited in a transaction.
• For tests that commit or use multiple connections, use unique fixture data and explicit cleanup in an isolated database.
• Confirm a failed test does not return a connection with an open or aborted transaction to the pool.
• Test timeout and cancellation paths without leaving long-running sessions behind.

Failure-path checklist

Test constraint violations, deadlock/serialization retry handling when relevant, connection loss, statement timeout, partial application failure, and idempotent retries. Inject faults only in a controlled test environment and verify the final persisted state.